Cumulative Totals over a period of time?

[aellistechsupport]

Yes, there won't be much if any as mentioned.

From the details I uploaded these are the May records:
I see an "overlap" for Season ending in 2015 of payments made in 2012 and 2013.
I think those two payments in 2013 were incorrectly entered.

All Collegiate members receive 4 years (4 seasons) of memberships.

MemberType	Season	MembershipNumber	PaymentDate	EndDate
Collegiate	2015	1144627	15-May-12	30-Jun-15
Collegiate	2015	1005705	16-May-12	30-Jun-15
Collegiate	2015	1099838	16-May-12	30-Jun-15
Collegiate	2015	6436112	07-May-13	30-Jun-15
Collegiate	2015	1252333	28-May-13	30-Jun-15
Collegiate	2016	6302859	01-May-13	30-Jun-16
Collegiate	2016	8919316	01-May-13	30-Jun-16
Collegiate	2017	1060155	02-May-14	30-Jun-17
Collegiate	2017	8746552	03-May-14	30-Jun-17
Collegiate	2017	1068751	07-May-14	30-Jun-17
Collegiate	2017	1276092	09-May-14	30-Jun-17
Collegiate	2017	6512612	15-May-14	30-Jun-17
Collegiate	2017	1530334	25-May-14	30-Jun-17
These too: Collegiate 2015 1131963 28-Jun-12 30-Jun-15 Collegiate 2015 1257570 11-Jun-13 30-Jun-15 Collegiate 2015 1257585 11-Jun-13 30-Jun-15
Collegiate 2016 6450162 06-Jun-13 30-Jun-16 Collegiate 2016 1277151 10-Jun-13 30-Jun-16 Collegiate 2016 1504335 11-Jun-13 30-Jun-16 Collegiate 2016 6005880 11-Jun-13 30-Jun-16 Collegiate 2016 6015490 11-Jun-13 30-Jun-16 Collegiate 2016 6027921 11-Jun-13 30-Jun-16 Collegiate 2016 6302489 11-Jun-13 30-Jun-16 Sorry, I'm not following how the formula captures any payments made in May or Jun?

[rpeare]

Jul: Sum(IIf((DatePart("m",[paymentdate])=7 And [paymentdate]>=DateSerial(([season]-4),7,1)) Or ([paymentdate]<DateSerial(([season]-4),7,1)),1,0))
Aug: Sum(IIf((DatePart("m",[paymentdate]) between 7 and 8 And [paymentdate]>=DateSerial(([season]-4),7,1)) Or ([paymentdate]<DateSerial(([season]-4),7,1)),1,0))

What part don't you understand

[aellistechsupport]

That formula shows:

SeasonEnd	Jul
2015	541
2016	511
2017	532
2018	629

Old formula shows:

SeasonEnd	Jul
2015	549
2016	520
2017	537
2018	629

Totaling up the details shows 549 for Season ending 2015 with payments from May and Jul in any of the years 4 years prior.
1st month of the fiscal year is showing correctly.

As long as a payment is made before the enddate they are applied to that enddate year.

[rpeare]

I thought we established the old formula was not doing what you wanted so why are we comparing to it?

Look at the breakdown by year I gave you several posts ago:

MemberType	Season	PayYear	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Collegiate	2015	2011	0	0	0	0	0	265	272	101	67	83	47	28
Collegiate	2015	2012	28	18	14	5	3	1	1	8	3	0	0	0
Collegiate	2015	2013	0	0	0	0	2	2	3	2	1	0	0	0

In july you should have all of july's totals (272 + 1 + 3) PLUS any sales prior to the start of the FY (265 in june of 2011)

That totals 541

[aellistechsupport]

No, all payments made for the Season goes is counted.

The only issue or issues with the current formula is / are:
1. Should it include Apr of the prior years when running in Apr? Right now it excludes them.
2. You brought up a point I'm not sure how to verify, if there'll be an issue next month in May and in Jun because the 1st column Jul, 1st month of the fiscal year is counting May and Jun?

[rpeare]

... I'm about to give up. You are not making sense to me at all. Every time I think I understand, you (to me) change your requirements

stop looking at one month look at your DATA again..........

MemberType	Season	PayYear	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Collegiate	2015	2011	0	0	0	0	0	265	272	101	67	83	47	28
Collegiate	2015	2012	28	18	14	5	3	1	1	8	3	0	0	0
Collegiate	2015	2013	0	0	0	0	2	2	3	2	1	0	0	0

this is how your data is spread out over the years of purchases for the 2015 season.

Explain, clearly, AND SHOW what you expect your final result to be and, if not immediately, apparent how you got your numbers.

[aellistechsupport]

Sorry,
again when it was paid doesn't matter as long as it was 4 years ago because Collegiate memberships are 4 years WHERE the Season ends in 2015 as you can see in the criteria under PaymentDate and EndDate.

I'll try to explain. In my head it makes sense especially when seeing the detail data set and finding what data is missing.
When the details of the month-to-month is displayed, the count of 1 in each monthly column, you'll notice the Apr is null in the prior years.

The PayYear is unnecessary. ALL of those payments for the Season ending in 2015 should be totaled into the month it was paid.
Given the parameter of the fiscal year and
payments made NOT only in the fiscal year of prior Jul-Jun BUT payments made since May prior to next year to Jun.

So your example totals 954, that is correct and that total should show in the Mar column but it's not with the current formula.
The original post, 1st post shows what it's doing. It's leaving out all of the Apr months payments in the prior years.

Instead of March for 2015 Season showing 954 it's showing 949. So the 5 payments from Apr with a Season ending in 2015 are missing.

MemberType	Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2015	549	660	731	814	861	889	917	935	949	954	954	954
Collegiate	2016	520	617	716	795	822	836	856	874	887	889	889	889
Collegiate	2017	537	622	693	813	855	869	901	921	931	941	941	941
Collegiate	2018	628	721	826	888	936	972	1020	1042	1050	1050	1050	1050

I hope this made more sense. Seeing the details, as mentioned, will help make sense of what I'm trying to convey.

Thanks for your patience.

NOTE here's my the main part of the original post:

Here is the summary. So All payments from Jun 2011 from Season 2015 through Mar 2015 to Season 2018 where the Mar column totals 3834. But only shows 3817.

Please see attached details. Collegiate.zip

MemberType	Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2015	549	660	731	814	861	889	917	935	949	954	954	954
Collegiate	2016	520	617	716	795	822	836	856	874	887	889	889	889
Collegiate	2017	537	622	693	813	855	869	901	921	931	941	941	941
Collegiate	2018	628	721	826	888	936	972	1020	1042	1050	1050	1050	1050

It seems, when I looked at the details and all the Apr totals for the prior years are being excluded. These 17 out of the 3834 and while it only says there are 3817. However, these 17 records, since it's past, should be included.

MemberType	GrandTotal	ReportingMonth
Collegiate	3834	Mar

[aellistechsupport]

One thought was, since it's reporting up to the prior month, in this case, Mar, the Apr should be left out OR
Since those months in the prior years have already passed, it SHOULD include the past Apr in the cumulative totals EXCEPT for last Season 2018 since Apr of this year hasn't passed or any payments made in the month of Apr even in the past since they are payments for Season 2018?

I'm not quite sure which way to view it and is more correct, thoughts?

Not sure about the May and Jun months in the front and then within the fiscal year.

[rpeare]

not clear.
still don't understand.
still haven't given an example of what you want the data to look like based on the root data (for 2015) only repeated the results of a query you posted on post #1 as far as I can tell

If you can not give a final result you want to see for the 2015 season I can't help you. Your use of jargon, while it may make sense to you, is close to gibberish to me. You are trying to establish a rule for a formula you can use for ANY season and you are fixated on which month is showing or not showing the rules of which continue to elude me.

Be specific like:
For the 2015 season, any ticket sold prior to 7/1/2011 should appear in the JUL column, Any ticket sold after 6/30/2012 should be shown in the JUN column, all other ticket sales should be shown in the month of their sale.

[aellistechsupport]

Sorry, I really don't know how to state what I've stated in various ways anymore differently to be understood.

As stated it's payments from 4 years ago is relevant to the current Season and 4 Seasons from the current Season. So 2015 to 2018 because memberships are good for 4 years.

If you look at the 02_Collegiate_Details.xlsc file in the original attachment, you'll see for all of Season 2015 that there are 954 paid membership records. Please filter on the Season column B on 2015. There are 954. Filter on the PaymentDate on Apr, you'll see there are 5 records in Apr. Subtract, as shown in the table result, 954 from Apr column with the 949 in the Mar column, that's these 5 records.

What I've been stating, the Apr months of prior year payments are not showing. And I'm not sure IF they should since they are in the past or since the report is reporting til before Apr the Apr data in the prior years should not show. If the latter is more accurate reporting, then the formula that I have now is fine.
However if that's not the case, then I need help modifying to include the Apr in the prior months in this month's running of the report.

Now take the other .xlsx file named 02_Collegiate.xlsx that's also in the original attachment. You should then notice that there are actually 3834 records HOWEVER only 3817 are totaling under the Apr column.

Because, as mentioned, the Apr data of the prior years are not being counted.

										3817
Collegiate	2015										1	1	1	01-Apr-12	942501
Collegiate	2015										1	1	1	06-Apr-12	1395682
Collegiate	2015										1	1	1	08-Apr-12	1486428
Collegiate	2015										1	1	1	17-Apr-12	6007168
Collegiate	2015										1	1	1	27-Apr-12	8762208
Collegiate	2016										1	1	1	20-Apr-13	1040804
Collegiate	2016										1	1	1	30-Apr-13	924553
Collegiate	2017										1	1	1	03-Apr-14	1482073
Collegiate	2017										1	1	1	03-Apr-14	6303630
Collegiate	2017										1	1	1	11-Apr-14	1438341
Collegiate	2017										1	1	1	12-Apr-14	1537156
Collegiate	2017										1	1	1	13-Apr-14	1126299
Collegiate	2017										1	1	1	14-Apr-14	6323641
Collegiate	2017										1	1	1	23-Apr-14	1063631
Collegiate	2017										1	1	1	23-Apr-14	1063633
Collegiate	2017										1	1	1	23-Apr-14	1063707
Collegiate	2017										1	1	1	23-Apr-14	1063708
											17

That's 3817 under the Mar column, add the 17 shown for the month of Apr in prior is, you get the grand total of ALL records in the detail file of 3834.

So should these 17 records for Apr show or NOT show when reporting for upto Mar?
1. Since those are dates in the past
2. OR they shouldn't because the report is only reporting upto Mar regardless of payment year?

Does this clear it up any better? Because I seriously do not know how else to state these in yet another way
And if you're unable to view the data set in the attachment I'm not sure how else to get you the 3834 records.

[aellistechsupport]

As for the 1st column Jul, as also noted,

SeasonEnd	Jul
2015	549
2016	520
2017	537
2018	629

Totaling up the details shows 549 for Season ending 2015 with payments from May and Jul in any of the years 4 years prior.
1st month of the fiscal year is showing correctly.

As long as a payment is made before the enddate they are applied to that enddate year.

So if you'd please filter on Season 2015 again then look for all payment dates from May - Jul ENDING in Season 2015 from the 02_Collegiate_Details.xlsx you'll see what I'm explaining.

Thank you

[rpeare]

there is still no example of what you expect the numbers to be for the 2015 season for each month you are explaining things the same way which is getting us nowhere. Go through your data, by hand if necessary, and actually fill in the numbers you would expect to see in every 'month' column for the 2015 season.

[aellistechsupport]

Showed you the first column Jul, those numbers for Season 2015 is correct when you filter on it with the details that's in the attachment.
The Mar column is questionable. Should it be 949 or 954. IF you include Apr it should be 954 if not it's 949.

Where these Apr membership payments are currently excluded with the current formula

Collegiate	2015										1	1	1	01-Apr-12	942501
Collegiate	2015										1	1	1	06-Apr-12	1395682
Collegiate	2015										1	1	1	08-Apr-12	1486428
Collegiate	2015										1	1	1	17-Apr-12	6007168
Collegiate	2015										1	1	1	27-Apr-12	8762208

On one hand I think it should be included since it was paid in 2012 but then again, since we are looking at result up to Mar only, should the really.
I'm not completely sure on that.

You have Jul and Mar to go by. And those have been identified clearly based on the details when filter on Season 2015.
All the other months in between are correct IF it should leave the month it's reporting out or not like for Apr.

Again, Those numbers have been verified. Filtered out by Season year and by PaymentDate and counted. Don't know what more to provide. You have the detailed file, you'll have to filter it verify what I'm stating and showing or take my word for it.

Ultimately I suppose is IF you were looking at the report what would you expect it to tell you? Would you want to see Apr data in the Apr column of the past payments or not?

I'll be asking management as well for clarification.

I'll get back to you on the May and Jun (the last two month of fiscal years)

[rpeare]

Sorry, I can't help you. I've told you what I need to be able to understand your problem and you are not providing it.

[aellistechsupport]

For May and Jun, since it's required at the beginning of the fiscal year in Jul and then at the last 2 months of the fiscal year:

So for Season 2015 the 3 May-12 payments should be reporting in the Jul column and the 2 May-13 payments should not be but show later in the May column.

MemberType	Season	MembershipNumber	PaymentDate	EndDate
Collegiate	2015	1144627	05/15/12	06/30/15
Collegiate	2015	1005705	05/16/12	06/30/15
Collegiate	2015	1099838	05/16/12	06/30/15
Collegiate	2015	6436112	05/07/13	06/30/15
Collegiate	2015	1252333	05/28/13	06/30/15