Exclude when the current month EXCEPT when?

[Solo712]

Sorry, let's just go with verifying your formula for other months.

Have you downloaded the database? I attached it making sure it has what I'm talking about so it makes sense from what I'm stating here to the actual queries and formulas in the db.

For purpose of testing
There is one table, as you have identified, it is "02_Collegiate_Details".

The queries to look at has been specified in the last post to you:
"ByFiscalYear" and "Query1"

The Query1, with the formula you have provided, should result in the same totals as shown in the query ByFiscalYear. And it does.
This month's total, using your formula in Query1, does reconcile to the total in the query ByFiscalYear in columns Apr, May, and Jun. So this is good!

So in trying to verify that your formula works in other months I modified it to

<>MonthDate())-1 or 5 or 6

and this still does reconcile to the column Mar in the query ByFiscalYear. This is still good!

However, in testing one more month prior, so I change the formula to

<>MonthDate())-2 or 5 or 6

BUT it does NOT reconcile to the column Feb in the query ByFiscalYear.

As shown in the prior post
with the

<>Month(Date())-2 or 5 or 6

Not sure why?

The difference is in that your ByFiscalYear formulas sweep the year going forward, where the Query1 formula includes the totals of months that are ahead, so the Month(Date())-2 case includes the April totals since they are not excluded by the formula. They are 5,2,10,5 in the four years queried.

Best,
Jiri

 

[helpangel]

Humh, ok.

How do I get them to match for each month?

I was thinking that as long as I have exclude current month except when it's May or Jun it would match.
The Mar and Apr columns do match but NOT Feb.

When I do

<>Month(Date())-3 or 5 or 6

I get: which is also incorrect.
Season Total
2015 936
2016 871
2017 921
2018 1033

so far only

<>Month(Date())-1 or 5 or 6

and

<>Month(Date()) or 5 or 6

are correct

 

[helpangel]

Since I need it to match the query ByFiscalYear, then ...

Can I change this formula to get it to match? If so what's the correct formula to achieve this?

 ToDate: IIf(Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=[B][I]currentmonth[/I][/B],1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=[I][B]currentmonth[/B][/I],1,0)))

Goal: To have the totals match from the Query1 to the ByFiscalYear each month when reporting in the current month.

 

[Solo712]

Since I need it to match the query ByFiscalYear, then ...

Can I change this formula to get it to match? If so what's the correct formula to achieve this?

 ToDate: IIf(Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=[B][I]currentmonth[/I][/B],1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=[I][B]currentmonth[/B][/I],1,0)))

Goal: To have the totals match from the Query1 to the ByFiscalYear each month when reporting in the current month.

That I am afraid will not work, as you are summing the aggregate membership by month but over the four years. Problem is that Query1 cuts it up by "season" and in the setup, season is defined as going from July of one year to Jun the next. This creates the boggle as to what fiscal year the upper months belong. So first you'll need to order the calendar months into fiscal months (like calendar 7/14=fiscal 1/15, 8/14=2/15...12/14=6/15, 1/15=7/15, etc) and then you can exclude the current month, the ones that follow and month 5 and 6 which will be fiscal 11 and 12. That is if you want to balance with byFiscalYear.

Best,
Jiri

 

[helpangel]

?
Both queries are grouped by Season though?

The report will always look at 4 seasons with 4 years of membership payments since these Collegiate memberships are for 4 season (4 years).
From current season year + 4
2015-2018 this year
2016-2019 next year and so on

 

[JHB]

Lol! Sorry.

Ok, so this looks like it works but, sorry, not sure how to test it?

IIf(Month(Date())=5 Or Month(Date())=6,True,IIf(Month([PaymentDate])=5 Or Month([PaymentDate])=6,False,True))=True

Full SQL

TRANSFORM Count([02_Collegiate_Details].MembershipNumber) AS CountOfMembershipNumber
SELECT [02_Collegiate_Details].Season, Count([02_Collegiate_Details].membershipnumber) AS Total
FROM 02_Collegiate_Details
WHERE ((IIf(Month(Date())=5 Or Month(Date())=6,True,IIf(Month([PaymentDate])=5 Or Month([PaymentDate])=6,False,True))=True))
GROUP BY [02_Collegiate_Details].Season
ORDER BY [02_Collegiate_Details].Season
PIVOT Format([PaymentDate],"mmm");

I want to test it as if it's last month and 2 months ago to see if the totals are correct with this formula. How / what can I change to test that?

Your formula is reconciling but not sure how to see if the prior months will reconcile as well to confirm this formula works.

For testing it for other month as the month we are in, change your computer data and time settings to another.
But like plog, I honestly do not know what results you actually expect to get, so I'm not able to control it, (what I know is, that the code include month May or June if the current month is May or June), when you exclude data for some months, you can't expect that the total result will be the same as if they are included.
What should the below return?

IIf(DatePart("m",[paymentdate])>=5 And 7

 

[Solo712]

?
Both queries are grouped by Season though?

The report will always look at 4 seasons with 4 years of membership payments since these Collegiate memberships are for 4 season (4 years).
From current season year + 4
2015-2018 this year
2016-2019 next year and so on

My dear friend,
this does not look like a productive exercise. If you examine the expressions in the byFiscalYear query closely, you'll find they vary from month to month to cope with the issue I outlined. Look at Nov, Dec, and Jan as examples of the variations. You would then have to vary those expressions accordingly in Query1 which leads to formulas which I have tried to avoid all my programming life. I wish you the best of luck !

Best,
Jiri

 

[helpangel]

I'm further now then before but stuck in verifying past Dec.
Any idea?

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5  And 7 Or  DatePart("m",[PaymentDate])<=Month(Date())-6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5  And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-6,1,0)))

If I do -7 or -8, etc ... to take me back to last Jul, I get the same total as I did for Dec at 3566. Not sure why?


It appears the formula works from Dec and on but not prior to Dec it does not. I am unsure why?


Full SQL where it seems to reconcile from the Dec column to at least the Apr column. However prior to Dec it doesn't work with the

<>Month(Date())-# or 5 or 6

 

[helpangel]

Solo712,

If there's another and better way, I'm open to it since I am stuck with what I have.
I just don't get how it works for several of the months but doesn't work for the other months?

 

[Solo712]

Solo712,

If there's another and better way, I'm open to it since I am stuck with what I have.
I just don't get how it works for several of the months but doesn't work for the other months?

But I told you, didn't I ? At any rate, the solution is in the attached file (query 'IIF3'). Works for all months !

Best,
Jiri

 

[helpangel]

But I told you, didn't I ? At any rate, the solution is in the attached file (query 'IIF3'). Works for all months !

Best,
Jiri

??
It's doing the same thing, it's showing the same totals as Dec prior to Dec? This is where I am stuck. It reconciles from Dec on but prior to Dec they do not. It just repeats the totals from the Dec column for Nov, Oct, etc ...

ToDate: IIf(Month(Date())<7,IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-9,1,0))),IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())-9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())-9,1,0))))

IIF3
ToDate
889 836 869 972

ByFiscalYear
Oct Nov Dec
814 861 889
795 822 836
813 855 869
888 936 972

Verified from Dec, Jan, Feb, Mar, Apr, etc ...
But going backward from Nov, Oct, etc ..., why do they not reconcile?

It should show these, where from the Dec column on do match but the prior months do not with the said formula.
Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May
549 660 731 814 861 889 917 935 949 954 954
520 617 716 795 822 836 856 874 887 889 889
537 622 693 813 855 869 901 921 931 941 941
628 721 826 888 936 972 1020 1042 1050 1055 1055

 

[Solo712]

??
It's doing the same thing, it's showing the same totals as Dec prior to Dec? This is where I am stuck. It reconciles from Dec on but prior to Dec they do not. It just repeats the totals from the Dec column for Nov, Oct, etc ...

ToDate: IIf(Month(Date())<7,IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-9,1,0))),IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())-9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())-9,1,0))))

IIF3
ToDate
889 836 869 972

ByFiscalYear
Oct Nov Dec
814 861 889
795 822 836
813 855 869
888 936 972

Verified from Dec, Jan, Feb, Mar, Apr, etc ...
But going backward from Nov, Oct, etc ..., why do they not reconcile?

It should show these, where from the Dec column on do match but the prior months do not with the said formula.
Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May
549 660 731 814 861 889 917 935 949 954 954
520 617 716 795 822 836 856 874 887 889 889
537 622 693 813 855 869 901 921 931 941 941
628 721 826 888 936 972 1020 1042 1050 1055 1055

You forgot to modify the first occurence of Month(Date()). You have to change the month everywhere when testing to get the result you want. BTW, the month argument needs to be 1 to 12 , no negative numbers !


Jiri.

 

[helpangel]

Sorry that I am lost ...

I get the correct numbers from the Dec through Apr columns using the formula

and by change it from -5 to -6

ToDate: IIf(Month(Date())<7,IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-6,1,0))),IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())-6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())-6,1,0))))

to check Nov, it just repeats Dec.

So I am not following
Sorry for not understanding this, with my limitation, what I see is that the totals match up until I go back to test for Nov and having verified that Dec - Apr do match, I'm just not understanding and lost.

 

[Solo712]

Sorry that I am lost ...

I get the correct numbers from the Dec through Apr columns using the formula

and by change it from -5 to -6

ToDate: IIf(Month(Date())<7,IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-6,1,0))),IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())-6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())-6,1,0))))

to check Nov, it just repeats Dec.

So I am not following
Sorry for not understanding this, with my limitation, what I see is that the totals match up until I go back to test for Nov and having verified that Dec - Apr do match, I'm just not understanding and lost.

Bud, before anything else I need to see some thank-you clicks on my replies in this thread for the hours that I spent on analyzing and solving this. At this time I need to know that my time is being appreciated.

Best,
Jiri

 

[helpangel]

Bud, before anything else I need to see some thank-you clicks on my replies in this thread for the hours that I spent on analyzing and solving this. At this time I need to know that my time is being appreciated.

Best,
Jiri

Ah, yes, sorry, your post #33 has been marked with Thanks

Not sure though, we are getting the same results from what I've been getting thus far.

 

[Solo712]

Ah, yes, sorry, your post #33 has been marked with Thanks

Not sure though, we are getting the same results from what I've been getting thus far.

Now, listen: I have given you a working solution that works for all months of the year. The problem you have NOW is that you are not testing the months properly. I told you: NO NEGATIVE NUMBERS ! When you write something like Month(Date()) - 6 (in May) it will evaluate to -1 and that screws the formula. There is no month -1. Months' ordinals are 1 to 12 and the formula will ONLY work correctly with that set of numbers. If you think that going negative is is the way to get to last year's November, that won't work ! Ever ! Worse still, it fucks up the switch that I put in specifically to flip between the two years. So, to replicate the byFiscalYear query for the accumulations to individual months you must test this way (while we are in the month of May):

For Jan -> Month(Date) - 4
For Feb -> Month(Date) - 3
For Mar -> Month(Date) - 2
:
:
For Jul -> Month(Date) + 2
:
For Nov -> Month(Date) + 6
For Dec -> Month(Date) + 7

In this way you are assuring the [currentmonth] is within the limit of 1- 12. No go and test it !

Good luck !

Jiri.

 

[helpangel]

Now, listen: I have given you a working solution that works for all months of the year. The problem you have NOW is that you are not testing the months properly. I told you: NO NEGATIVE NUMBERS ! When you write something like Month(Date()) - 6 (in May) it will evaluate to -1 and that screws the formula. There is no month -1. Months' ordinals are 1 to 12 and the formula will ONLY work correctly with that set of numbers. If you think that going negative is is the way to get to last year's November, that won't work ! Ever ! Worse still, it fucks up the switch that I put in specifically to flip between the two years. So, to replicate the byFiscalYear query for the accumulations to individual months you must test this way (while we are in the month of May):

For Jan -> Month(Date) - 4
For Feb -> Month(Date) - 3
For Mar -> Month(Date) - 2
:
:
For Jul -> Month(Date) + 2
:
For Nov -> Month(Date) + 6
For Dec -> Month(Date) + 7

In this way you are assuring the [currentmonth] is within the limit of 1- 12. No go and test it !

Good luck !

Jiri.

Ok, I did not know that. Thank you!
I just tested with the +6 and +7 and it still results to the same totals as the Dec. totals??

IIF3
Total
954
889
941
1055

With:

Total: IIf(Month(Date())<7,IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())+6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())+6,1,0))),IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())+6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())+6,1,0))))

 

[Solo712]

Ok, I did not know that. Thank you!
I just tested with the +6 and +7 and it still results to the same totals as the Dec. totals??

IIF3
Total
954
889
941
1055

With:

Total: [COLOR=red]IIf(Month(Date())<7[/COLOR],IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())+6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())+6,1,0))),IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())+6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=Month(Date())+6,1,0))))

Should be : IIf(Month(Date()) + 6 <7

Best,
Jiri

 

[helpangel]

BRILLIANT!
THANK YOU SO VERY MUCH for the SOLUTION!

Thank you also for your patience with me. I have a lot to learn still

 

[Solo712]

BRILLIANT!
THANK YOU SO VERY MUCH for the SOLUTION!

Thank you also for your patience with me. I have a lot to learn still

You are very welcome. Sorry, didn't mean to be cranky.

Could you mark the thread [Solved] ? Thanks.

Best,
Jiri