Cumulative or rolling percentages at a month level from week over week data

[JamesDeckert]

Now that I'm looking at your pics, I don't think the cumproposals field is working the way I planned. From post #13, mary for week 2 should be 33, not 3. If we get that formula working I think you'll be there. You should group by week (I was wrong earlier). You don't need to count() in cumproposals


Why did you change "cumproposals: dsum("[proposals]","proposalstable"...." so that it's not using the [proposals] field? I don't understand the change

[mindbender]

when I had it just looking at the proposals field it did not seem to calculate properly. This is what the formula looks like
.

Here is what it returns

[JamesDeckert]

Can you manually look at the table and see if the Proposals is correct for the current week. So does Mary have 3 proposals for week 2 and 30 for week 1? If this is correct, I don't know why the cumproposals formula is only getting the current weeks total and not the total from everything prior to and equal to the current week. Shelly's week 2 should be 26 (I'm assuming).
Can you show the formula for [week] again? And can you open the table and show VOSS and mifmgtstatusdate data. Sort by VOSS and show all the records for Mary in your next post.
Something that you can do is to put the cumproposals formula in the immediate window and see if it's giving results that are correct (e.g. Mary for week 2 is 33). [voss] would need to be replaced by Mary Stevenson and [week] be replaced by 2.

[mindbender]

MIF_MGMT_DT - Copy.ziphope I ma not being too forward but I attached a stripped down version of the db with the table and the query in question. Thought maybe it would give you more control to look into things

but the numbers returned in the query are correct

[JamesDeckert]

I think I got what you are wanting.
I changed a few things, below are the formulas and the complete SQL. I also attached the changed DB.
The problem with cumproposals was that we weren't including all the criteria necessary (month/year/recentstatus), as well as trying to use [proposals] which was calculated. I went back to the original field [RowID] and counted.

cumproposals: DCount("[RowID]",'tblMIF_MGMT',"[VOSS]='" & [voss] & "' and (day([mifmgtstatusdate]-1)\7+1) <=" & [Week] & " And Month([mifmgtstatusdate])=Month(Now()) And Year([mifmgtstatusdate])=Year(Now()) and [MIF Mgt Most Recent Status]='Proposal Presented'") (expression)

Prct: [cumproposals]/([week]*40) (expression)

WklyProposals: Rowid (count)

SELECT tblMIF_MGMT.VOSS, MonthName(Month([MIFMgtStatusDate])) AS [Month], (Day([MIFMgtStatusDate])-1)\7+1 AS Week, DCount("[RowID]",'tblMIF_MGMT',"[VOSS]='" & [voss] & "' and (day([mifmgtstatusdate]-1)\7+1) <=" & [Week] & " And Month([mifmgtstatusdate])=Month(Now()) And Year([mifmgtstatusdate])=Year(Now()) and [MIF Mgt Most Recent Status]='Proposal Presented'") AS cumproposals, [cumproposals]/([week]*40) AS Prct, Count(tblMIF_MGMT.Rowid) AS WklyProposals
FROM tblMIF_MGMT
WHERE (((tblMIF_MGMT.[MIF Mgt Most Recent Status])="Proposal Presented"))
GROUP BY tblMIF_MGMT.VOSS, Year([MIFMgtStatusDate]), MonthName(Month([MIFMgtStatusDate])), Month([MIFMgtStatusDate]), (Day([MIFMgtStatusDate])-1)\7+1
HAVING (((Year([MIFMgtStatusDate]))=Year(Now())) AND ((Month([MIFMgtStatusDate]))=Month(Now())));

MIF_MGMT_DT - Copy (2).zip

BTW: part of the problem was that this DB is a flat file instead of a relational one.

[mindbender]

that is AWESOME!!!!! Thanks so much. I have learned a lot from this and I can now apply it to other columns in the database as well.
I can't thank you enough for the time you took to help me out.
I really appreciate it

Bret

[mindbender]

Do I need to do something different when there is more than 1 criteria ?

I changed = Proposal Presented to Like 'F2F Call Complete' Or Like 'Order Signed' Or Like 'Proposal Presented' Or Like 'VQ/CSA Created' Or Like 'XOA Delivered' Or Like 'Site Visited' Or Like 'Other-comment' Or Like 'Offer Declined' Or Like 'Validated Only'

and I get #ERROR For cumproposals and Prct

[JamesDeckert]

copy your code in your post for cumproposals. The key is that your cumproposals formula much match the criteria which you have for the rest of the query. Otherwise it won't total the way the query is totaling.
why are you using 'like'?
when using like you normally include some kind of wildcard character (e.g. like "abc*" to get words starting with abc)

[mindbender]

Yeah I caught that. Here is what I have now and it does return data

cumproposals: DCount("[RowID]",'tblMIF_MGMT',"[VOSS]='" & [voss] & "' and (day([mifmgtstatusdate]-1)\7+1) <=" & [Week] & " And Month([mifmgtstatusdate])=Month(Now()) And Year([mifmgtstatusdate])=Year(Now()) AND ([MIF Mgt Most Recent Status]='F2F Call Complete' Or [MIF Mgt Most Recent Status]= 'Order Signed' Or [MIF Mgt Most Recent Status]= 'Proposal Presented' Or [MIF Mgt Most Recent Status]= 'VQ/CSA Created' Or [MIF Mgt Most Recent Status]= 'XOA Delivered' Or [MIF Mgt Most Recent Status]= 'Site Visited' Or [MIF Mgt Most Recent Status]= 'Other-comment' Or [MIF Mgt Most Recent Status]= 'Offer Declined' Or [MIF Mgt Most Recent Status]= 'Validated Only')")

But a weird thing I cannot seem to nail down are the results for 1 person

If you look at the numbers below you will see Mary Stevenson looks perfect but if you scroll down at Shelley Forgey for some reason the numbers don't match between the Count of rowed (WklyProposals) and the cumproposals. I have looked in the table and the weekly number (49) is correct. I also looked to see if for some reason there were duplicate rowids in case the DCount was a distinct count - that is not the case either.

Very weird

[mindbender]

forgot the pic

[JamesDeckert]

Can you copy your entire SQL in the post so I can recreate it here. The wklypropos is dependant on how you have the criteria set, so would like to get it exact and not guess.

BTW: does [MIF Mgt Most Recent Status] have a fixed number of options and are you checking for all options except for "Proposal Presented". If so it would be much shorter to use AND NOT [MIF Mgt Most Recent Status] = "Proposal Presented".

[mindbender]

SELECT tblMIF_MGMT.VOSS, MonthName(Month([MIFMgtStatusDate])) AS [Month], (Day([MIFMgtStatusDate])-1)\7+1 AS Week, DCount("[RowID]",'tblMIF_MGMT',"[VOSS]='" & [voss] & "' and (day([mifmgtstatusdate]-1)\7+1) <=" & [Week] & " And Month([mifmgtstatusdate])=Month(Now()) And Year([mifmgtstatusdate])=Year(Now()) AND ([MIF Mgt Most Recent Status]='F2F Call Complete' Or [MIF Mgt Most Recent Status]= 'Order Signed' Or [MIF Mgt Most Recent Status]= 'Proposal Presented' Or [MIF Mgt Most Recent Status]= 'VQ/CSA Created' Or [MIF Mgt Most Recent Status]= 'XOA Delivered' Or [MIF Mgt Most Recent Status]= 'Site Visited' Or [MIF Mgt Most Recent Status]= 'Other-comment' Or [MIF Mgt Most Recent Status]= 'Offer Declined' Or [MIF Mgt Most Recent Status]= 'Validated Only')") AS cumproposals, Count(tblMIF_MGMT.Rowid) AS WklyProposals, [cumproposals]/([week]*150) AS Prct
FROM tblMIF_MGMT
WHERE (((tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'F2F Call Complete' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Order Signed' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Proposal Presented' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'VQ/CSA Created' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'XOA Delivered' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Site Visited' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Other-comment' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Offer Declined' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Validated Only'))
GROUP BY tblMIF_MGMT.VOSS, (Day([MIFMgtStatusDate])-1)\7+1, Year([MIFMgtStatusDate]), Month([MIFMgtStatusDate])
HAVING (((Year([MIFMgtStatusDate]))=Year(Now())) AND ((Month([MIFMgtStatusDate]))=Month(Now())));

I wish it was that easy There are many values in the Recent Status Field and this one looks at just a certain number (more than 1) where as Proposal Presented query was just looking at the single value