topic Re: Set analysis date range in QlikView

Set analysis date range

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Set analysis date range

sunny_talwar — Wed, 21 Dec 2016 15:49:43 GMT

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

Re: Set analysis date range

bobbydave — Wed, 21 Dec 2016 15:51:53 GMT

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.

Re: Set analysis date range

sunny_talwar — Wed, 21 Dec 2016 15:55:52 GMT

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?

Re: Set analysis date range

bobbydave — Wed, 21 Dec 2016 15:57:08 GMT

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.

Re: Set analysis date range

bobbydave — Wed, 21 Dec 2016 15:59:28 GMT

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

Re: Set analysis date range

sunny_talwar — Wed, 21 Dec 2016 16:03:21 GMT

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

Re: Set analysis date range

bobbydave — Thu, 22 Dec 2016 09:06:38 GMT

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