Cumulative Sum of Counts for Static Months

[aellistechsupport]

I'm in need of some assistance with trying to get a query, or actually end result would be as a report, to display cumulative sum of counts for each month, rolling months.
And of course would like to have the titles/labels display the month name as the label.

I was able to find some information however I am getting an error and I am unsure how to combine what I've found to display in the final result with the month labels.

Here is what I have so far and I am getting an #Error:

RunTot: DSum(Count([attribute_PersonMembership].[PersonId]),"DatePart('mm',[PaymentDate])<=" & [SortMonth] & " And  DatePart('yyyy',[PaymentDate])<=" & [Year] & "")

Which I've come across that the MS article this came from http://support.microsoft.com/kb/290136 is incorrect in the first place

Here's the full query:

SELECT lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description AS MemberType, Format([PaymentDate],"yyyy") AS [Year], Format([PaymentDate],"mm") AS SortMonth, Format([PaymentDate],"mmm") AS [Month], DSum(Count([attribute_PersonMembership].[PersonId]),"DatePart('mm',[PaymentDate])<=" & [SortMonth] & " And  DatePart('yyyy',[PaymentDate])<=" & [Year] & "") AS RunTot, Count(attribute_PersonMembership.PersonId) AS NoOfMembershipsFROM lookup_MemberTypes INNER JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
WHERE (((lookup_MemberTypes.IsIndividual)<>-1) AND ((attribute_PersonMembership.PaymentDate)>=DateAdd("yyyy",-1,Date())) AND ((lookup_MemberTypes.Id)=1 Or (lookup_MemberTypes.Id)=2 Or (lookup_MemberTypes.Id)=4))
GROUP BY lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description, Format([PaymentDate],"yyyy"), Format([PaymentDate],"mm"), Format([PaymentDate],"mmm")
HAVING (((lookup_MemberTypes.MemberGroup)="Regular Member"));

Attempt at a cross-tab query and I get "Data Type Mismatch" error:

TRANSFORM DSum(Count([attribute_PersonMembership].[PersonId]),"DatePart('mm',[PaymentDate])<=" & [SortMonth] & " And  DatePart('yyyy',[PaymentDate])<=" & [Year] & "") AS RunTotSELECT lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description AS MemberType, Format([PaymentDate],"yyyy") AS [Year], Format([PaymentDate],"mm") AS SortMonth
FROM lookup_MemberTypes INNER JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
WHERE (((lookup_MemberTypes.MemberGroup)="Regular Member") AND ((lookup_MemberTypes.IsIndividual)<>-1) AND ((attribute_PersonMembership.PaymentDate)>=DateAdd("yyyy",-1,Date())) AND ((lookup_MemberTypes.Id)=1 Or (lookup_MemberTypes.Id)=2 Or (lookup_MemberTypes.Id)=4))
GROUP BY lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description, Format([PaymentDate],"yyyy"), Format([PaymentDate],"mm")
ORDER BY Format([PaymentDate],"mm")
PIVOT Format([PaymentDate],"mmm");

With the basic cross-tab query:

TRANSFORM Count(attribute_PersonMembership.PersonId) AS CountOfPersonIdSELECT lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description AS MemberType
FROM lookup_MemberTypes INNER JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
WHERE (((lookup_MemberTypes.MemberGroup)="Regular Member") AND ((lookup_MemberTypes.IsIndividual)<>-1) AND ((attribute_PersonMembership.PaymentDate)>=DateAdd("yyyy",-1,Date())) AND ((lookup_MemberTypes.Id)=1 Or (lookup_MemberTypes.Id)=2 Or (lookup_MemberTypes.Id)=4))
GROUP BY lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description
ORDER BY lookup_MemberTypes.Description
PIVOT Format([PaymentDate],"mmm");

I get this which is the way the report needs to display.
I would like a Report to display this BUT cumulative sums of the count AND displaying the rolling month labels each month in order:
This is showing the total counts for each month separately for the last 12 months.
So May should be the sum of Apr (122) + May (74). May column needs to show 122+74 = 196 and so on.


And next month it should start with May - Apr and so on.

MemberGroup
MemberType
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar

Regular Member
First Family
122
74
14955
8801
2321
1649
1775
849
330
382
333
209

Regular Member
Introductory
131
95
924
823
521
433
507
312
166
274
238
232

Regular Member
Subsequent Family
100
80
6698
4540
1228
731
875
454
185
194
191
157

I'm uploading the db.

The 3 queries and the report (which is in WIP for formatting) are in the db.

I'm thinking do the summing for each month in the report design?

Also, the report will always report from Jul (previous year) - Jun (current year).

[rpeare]

I'll take a look at this but in the meantime I have a couple of suggestions and observations.

1. Don't use Domain functions within queries if you can possibly avoid it, they are extremely resource intensive and will bog down/bloat your database pretty quickly
2. When you perform a crosstab query on a rolling month basis it looks like your columns are going to continue to be in the same order with april being the first month represented. So, for instance, if I were to change your paymentdate to >=dateadd("yyyy", -1, #2/1/2014#) will still get my months listed as April (far left) through March (far right). As long as you are good with your months always being in the same order you're fine, but if you wanted February to appear in the first column when you were producing a report for 2/2014 you have a problem.
3. Building reports on crosstabs is tricky for the reason listed above particularly if you want the rolling part to place the most recent month either at the far left or far right and arrange the other columns around that arrangement, typically I do this kind of thing with VBA, modifying the column header labels as necessary based on the current month.
4. With your date arrangement, if you run this in the middle of a month you are going to have to display 13 months not 12 if you want an accurate representation of your membership numbers otherwise you'll be representing half of the prior year's numbers and half of the current year's numbers, if this is a month end report you'd be better served by putting in a prompt for the date rather than using the system date, using something like [Enter the Month Ending Date]

EDIT: I just re-read your post and you said it was always from Jul of one year through Jun of the following year, which is not a rolling year, can you clarify? a rolling year would be something like producing this report every month during a year where it would display the 'current' month through the current month + 1 of the previous year

[aellistechsupport]

I'll take a look at this but in the meantime I have a couple of suggestions and observations.

1. Don't use Domain functions within queries if you can possibly avoid it, they are extremely resource intensive and will bog down/bloat your database pretty quickly
2. When you perform a crosstab query on a rolling month basis it looks like your columns are going to continue to be in the same order with april being the first month represented. So, for instance, if I were to change your paymentdate to >=dateadd("yyyy", -1, #2/1/2014#) will still get my months listed as April (far left) through March (far right). As long as you are good with your months always being in the same order you're fine, but if you wanted February to appear in the first column when you were producing a report for 2/2014 you have a problem.
3. Building reports on crosstabs is tricky for the reason listed above particularly if you want the rolling part to place the most recent month either at the far left or far right and arrange the other columns around that arrangement, typically I do this kind of thing with VBA, modifying the column header labels as necessary based on the current month.
4. With your date arrangement, if you run this in the middle of a month you are going to have to display 13 months not 12 if you want an accurate representation of your membership numbers otherwise you'll be representing half of the prior year's numbers and half of the current year's numbers, if this is a month end report you'd be better served by putting in a prompt for the date rather than using the system date, using something like [Enter the Month Ending Date]

Hello rpeare,
Thank you.

Ok, so on the suggestions / observations:
1. I'm sorry, I don't know what Domain functions are??
2. - 4. Found out it'll always be from prior year July to current year June to help format this.

Just the data for the months need to "roll" with the accumulated figures. This should make it easier for the month labeling?
So will have to put the date range parameter to always pull from last July to last month or current month for the purpose of reporting.

[rpeare]

Hah ok yes it will make it easier.

[rpeare]

Try this query:

SELECT lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description AS MemberType, Count(attribute_PersonMembership.PersonId) AS CountOfPersonId, Sum(IIf(DatePart("m",[paymentdate])=7,1,0)) AS Jul, Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0)) AS Aug, Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0)) AS Sep, Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0)) AS Oct, Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0)) AS Nov, Sum(IIf(DatePart("m",[paymentdate])>=7,1,0)) AS [Dec], Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=1,1,0)) AS Jan, Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=2,1,0)) AS Feb, Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=3,1,0)) AS Mar, Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=4,1,0)) AS Apr, Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=5,1,0)) AS May, Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=6,1,0)) AS Jun
FROM lookup_MemberTypes INNER JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
WHERE (((lookup_MemberTypes.MemberGroup)="Regular Member") AND ((lookup_MemberTypes.IsIndividual)<>-1) AND ((attribute_PersonMembership.PaymentDate) Between DateAdd("d",+1,DateAdd("yyyy",-1,[Enter the Date])) And [Enter the Date]) AND ((lookup_MemberTypes.Id)=1 Or (lookup_MemberTypes.Id)=2 Or (lookup_MemberTypes.Id)=4))
GROUP BY lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description
ORDER BY lookup_MemberTypes.Description

Note you have to manipulate the date in the criteria if you just subtract one year it won't be quite right (for instance if you put in 6/30/2014 and subtract 1 year you'll be going from 6/30/2013 through 6/30/2014) so you have to 1 day to the year subtraction to get 7/1/2013 through 6/30/2014.

[aellistechsupport]

Yup. So I was able to figure out how to limit the data from last July to end of last month.

Between DateAdd("yyyy",-1,DateSerial(Year(Date()),7,1)) And DateSerial(Year(Date()),Month(Date()-1),0)

So since that'll be static, meaning it'll always be July - June, I think I can figure out how to label these.


Now for the cumulative running total for each month. If you could help me with that, that would be greatly appreciated.

[aellistechsupport]

Try this query:

SELECT lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description AS MemberType, Count(attribute_PersonMembership.PersonId) AS CountOfPersonId, Sum(IIf(DatePart("m",[paymentdate])=7,1,0)) AS Jul, Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0)) AS Aug, Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0)) AS Sep, Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0)) AS Oct, Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0)) AS Nov, Sum(IIf(DatePart("m",[paymentdate])>=7,1,0)) AS [Dec], Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=1,1,0)) AS Jan, Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=2,1,0)) AS Feb, Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=3,1,0)) AS Mar, Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=4,1,0)) AS Apr, Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=5,1,0)) AS May, Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=6,1,0)) AS Jun
FROM lookup_MemberTypes INNER JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
WHERE (((lookup_MemberTypes.MemberGroup)="Regular Member") AND ((lookup_MemberTypes.IsIndividual)<>-1) AND ((attribute_PersonMembership.PaymentDate) Between DateAdd("d",+1,DateAdd("yyyy",-1,[Enter the Date])) And [Enter the Date]) AND ((lookup_MemberTypes.Id)=1 Or (lookup_MemberTypes.Id)=2 Or (lookup_MemberTypes.Id)=4))
GROUP BY lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description
ORDER BY lookup_MemberTypes.Description

Note you have to manipulate the date in the criteria if you just subtract one year it won't be quite right (for instance if you put in 6/30/2014 and subtract 1 year you'll be going from 6/30/2013 through 6/30/2014) so you have to 1 day to the year subtraction to get 7/1/2013 through 6/30/2014.

This works perfectly!! Thank you very very much!

[aellistechsupport]

ooh, one thing, is there a way to not display the numbers in the future months?

So right it's showing the same count from the current month, Apr, also in May and in Jun

[aellistechsupport]

Ok actually, not sure what it's showing. I thought initially it was repeating the figures from the current month

MemberGroup	MemberType	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	YTD
Regular Member	First Family	8801	11122	12771	14546	15395	15725	16107	16440	16649	16785	16859	31814	31814
Regular Member	Introductory	823	1344	1777	2284	2596	2762	3036	3274	3506	3646	3741	4665	4665
Regular Member	Subsequent Family	4540	5768	6499	7374	7828	8013	8207	8398	8555	8650	8730	15428	15428

The Apr, May, and Jun is pulling 2013's data

[rpeare]

Did you use my SQL or your own, my SQL was showing the same total in the April, May and June columns.

[aellistechsupport]

Did you use my SQL or your own, my SQL was showing the same total in the April, May and June columns.

Yes, I reapplied yours to verify. It's repeating the Mar data into Apr, May, Jun.

MemberGroup	MemberType	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Regular Member	First Family	8801	11122	12771	14546	15395	15725	16107	16440	16649	16649	16649	16649
Regular Member	Introductory	823	1344	1777	2284	2596	2762	3036	3274	3506	3506	3506	3506
Regular Member	Subsequent Family	4540	5768	6499	7374	7828	8013	8207	8398	8555	8555	8555	8555

[rpeare]

So is there an issue or are we good?

If you want a zero to appear in the columns for the months that haven't happened yet your formulas would get a bit more complex, for instance what if in, say october, you had no new introductory records, then in november you had more records would you really want october to be blank? or would you want the value to show that there was nothing new?

[aellistechsupport]

ah, gotcha.
Ok, I'm going to try and convince them to accept it

unless you can think of a way, perhaps in the Report design to not display future month columns? if that's easier?

Thank you so much again! It's brilliant! Compared to the MS link. At least for the static months and month labeling.

[rpeare]

You'd have to have a formula in each month's column like this instead:

IIf(Sum(IIf(DatePart("m",[paymentdate])=5,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=5,1,0)))

Where the number in bold red would have to be the month of the column you're trying to figure

[aellistechsupport]

Thank you! That seems to work!

You're brilliant

thank you tremendously!