Set analysis date range

[bobbydave]

I cannot understand why the below expression doesnt work

Sum({$<

    [Fiscal Year Month] ={">=$(=Date(AddMonths(Max(Today()),-8), 'YYYY-MM')) <=$(=Date(AddMonths(Max(Today()),-2), 'YYYY-MM'))"}   

       >} [Record Count])

The below does work.

Sum({<

    [Fiscal Year Month] = {"$(=Date(AddMonths(Max(Today()), -1), 'YYYY-MM'))"}>}

  [Record Count])

as i get the required figure.

I get 0 for

Sum({$<

    [Fiscal Year Month] ={">=$(=Date(AddMonths(Max(Today()),-8), 'YYYY-MM')) <=$(=Date(AddMonths(Max(Today()),-2), 'YYYY-MM'))"}   

       >} [Record Count])

Any one see the issue?

[sunny_talwar]

Do you have other date related fields where you might be making selections? Like Fiscal Month or something along those lines? If you do, then you need to ignore selections in those fields and let your Fiscal Year Month indirectly drive your selections in other date related fields

[bobbydave]

These dates for back to 2015-01 if thats what you mean.

If i use the working example of last month record count and then get today()-8, i will get the correct answer.

Its when i sum >= today() -8 and  today() -2 which is my issue.

[sunny_talwar]

That's not what I meant, I think.... Are you making any selections when it is not working? or is it not working regardless of any selection made or not?

[bobbydave]

Also, I was told before not to use 'AND' in set analysis

Sum({<

    [Fiscal Year Month]= {"=Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') >= AddMonths(Today(), -8)

         and Date#(Only({<[Fiscal Year Month]>}[Fiscal Year Month]), 'YYYY-MM') <= AddMonths(Today(), -2)"}>}

  [Record Count])

This however, does give me a figure which could be right. But i am dubious to use the expression if it is not proper procedure to use AND in set analysis.

[bobbydave]

Apologies for my misinterpretation.

No selections have been made.

It will always be these dates being used today -8 and today -2.

It is to be used in a condition for an Alert

[sunny_talwar · Accepted solution]

I don't see any issues with using this, but from the looks of the things, it seems that your Fiscal Year Month field is not interpreted as a date field? May be do like this in the script:

LOAD Date(Date#([Fiscal Year Month]), 'YYYY-MM'), 'YYYY-MM') as [Fiscal Year Month]

and then try your expression as is

Sum({$<[Fiscal Year Month] = {">=$(=Date(AddMonths(Max(Today()),-8), 'YYYY-MM'))<=$(=Date(AddMonths(Max(Today()),-2), 'YYYY-MM'))"}>} [Record Count])

[bobbydave]

YEEEEESSSSSSS.

Finally.

FINALLY.

Thank you so much.

It must have been a case of loading it in the script.

Got my value now.

Thanks again Sunny