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

[mindbender]

Yeah say that three time fast.

Here is what I am wondering if it is possible

I have my data broken down by week. Week 1, week 2 etc. The users have a weekly target of 40. What I would like to have is one column that would know to divide the week 1 number by 40, divide the week1+week2 number by 80 etc and show me a month to date rolling percentage of where they are against the target.

I hope I am not confusing everyone

[JamesDeckert]

Can you provide the table structure for the relevant fields?
I don't know if week 1, week 2, etc are the same field or different fields with another field noting which week it is.
Showing some example data would be good also.

[mindbender]

sure all of the date fields are driven off of one field MIFMgtStatusDate.
Month: MonthName(Month([MIFMgtStatusDate]))
Week#: (Day([MIFMgtStatusDate])-1)\7+1
Year([MIFMgtStatusDate])
VOSS - Text field
#Proposals - Text Field

I will try to attach a screen shot of the sample data as well as the query structure - see if it works

Thanks

[JamesDeckert]

What number are you comparing to 40, 80, etc? I'm not seeing a number of hours worked column.
[hoursworked]/[week#]*40
but hoursworked is not cumulative is it? It would just be the hours for a single week, so week 1 would work but not week 2, etc.
If this is the case you could cumhours: dsum("[hoursworked]","hrstable","[VOSS]=" & [voss] & " and (day([mifmgtstatusdate]-1)\7+1) <=" & [week#])
then you could [cumhours]/[week#]*40

[mindbender]

I am confused now.. basically they have a quota of 40 proposals they need to get in a week. So in the data Above if you look at the Data for Mary Stevenson here is what it should work out to be:
Mary week 1 - 6 proposals and her target was 40
Mary Week 2 - 6 proposals from week 1 + 24 from week 2 for a total of 30 and her target would be 80 (40x2)
Mary week 3 - 6 proposals from week 1 + 24 from week 2 + 8 from week 3 for a total of 38 and her target would be 120 (40x3)

so I would like to see something like this

on week 1 it would look like this:

Rep Week1 Week2 Week3 Week4 Week5 Month To Date % of Target
Mary 6 15%

on week 2 it would look like this:

Rep Week1 Week2 Week3 Week4 Week5 Month To Date % of Target
Mary 6 24 37.5%

and so on.

I hope this helps out

[JamesDeckert]

Oh, I thought it was 40 hrs/week, not proposals.
So something like this should work
If this is the case you could cumproposals: dsum("[proposals]","proposalstable","[VOSS]=" & [voss] & " and (day([mifmgtstatusdate]-1)\7+1) <=" & [week#])
then you could have another column to calc percentage [cumproposals]/[week#]*40

[mindbender]

I see where you are going but I may have screwed something up. I could not get it to work in the initial query (kept asking for field [Week] because it was not yet created) So I turned it into a make table query and then tried the dsum from there


But when I execute it I get this error looks like I may have screwed up the day calculation somehow


I appreciate all the help on this James

I just noticed I had the table wrong in the lower string but I got the same error when I updated it to the right table name

[JamesDeckert]

Thanks for showing the error. I see that Chris Willet doesn't have quotes around his name.
I left them out in my suggestion, try instead
cumproposals: dsum("[proposals]","proposalstable","[VOSS]='" & [voss] & "' and (day([mifmgtstatusdate]-1)\7+1) <=" & [week#])

[mindbender]

sorry for being so thick.

here is what I entered

here is what is returned. I does not include the reps names and when I added the column into the query for fun I noticed because I had to add the date field to the original query in order for this formula to work I had to use group by and max to get the numbers right



Am I confusing things or is there a way to all of this in 1 query? I seem to have to have multiple make table queries to get to the right level ?

[JamesDeckert]

You should be able to add this formula to a blank column on your post #3 query.
And another blank colum add the conversion to %, Prct:[cumproposals]/[week#]*40

[mindbender]

sorry for being so thick.

I put it in the original query and keep getting it looking for the Week field.

[mindbender]

I got it to work doing this

I removed the field Proposals and put its criteria in the dsum, then added brackets around [Week]*40 so it did that calculation before diving the number of proposals by it

Do you see anything I have done that will cause any issues going forward?

Thank you so much for the help

[mindbender]

but when I add data for week 2 it looks like below - which I am assuming is because the Week field has a group by on it which stops the 2 weeks from being added together for the cumproposals formula but I need the week number to show for the reporting by week in my pivot table.

[JamesDeckert]

you want cumproposals and prct to be expressions.
I think you're right about the week, it shouldn't be grouped, but instead make it an expression also.
You're right about [week]*40, it should either be [cumproposals]/([week#]*40) as you said or [cumproposals]/[week#]/40
See if this helps

[mindbender]

I am so close - I have tried just about everything in the Week column but I always seem to get this error. Only seems to go away when I hit group by