Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bobbydave
Creator III
Creator III

Set analysis date range

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?

1 Solution

Accepted Solutions
sunny_talwar

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])

View solution in original post

7 Replies
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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

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
Creator III
Creator III
Author

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