topic Re: rolling 3 months not working properly with set analysis...? in QlikView

rolling 3 months not working properly with set analysis...?

vishalgoud — Thu, 25 Feb 2016 13:59:08 GMT

hi experts,

am attaching the sample data , which is having a date field from that am extracting the month field.

and using the same month as dimension and exp as :

Exp : = only ({< MONTH = {'>= $(=Max(MONTH)-2) <=$(=Max(MONTH))'} > } Final_Result) .

I have the data for 3 years from 2013 to 2015 but the load order into Qlikview will be different.

Rolling 3 months not working fine when I select Jan ,Feb , Nov and dec of every year..

for the remaining month selections it is working fine...

please let me know your suggestions or best methods to achieve this..

Attaching the sample please have a look into that...

Best Regards,

Vishal.

Re: rolling 3 months not working properly with set analysis...?

sunny_talwar — Thu, 25 Feb 2016 14:36:07 GMT

Not sure what you trying to do, but may be this:

=Sum({<MonthNum = {$(=If(Max(MonthNum) > 2, Chr(39) & '>=' & (Max(MonthNum) - 2) & '<=' & Max(MonthNum) & Chr(39), If(Max(MonthNum) = 1, '1, 11, 12', '1, 2, 12')))}, Month>} Final_Result)

Re: rolling 3 months not working properly with set analysis...?

Oleg_Troyansky — Thu, 25 Feb 2016 14:51:39 GMT

This formula might be working, but it's a very wasteful calculation. What's the point in using Set Analysis if you have two nested IF statements in it?

The original calculation with Month can't work for two reasons:

1. In order to subtract 2 months, you need a field that goes across years, to avoid the need in several IF conditions. You need to prepare a field like MonthSerialNumber, where you assign a serial number to every month in your data, in a way that January 2016 is equal to December 2015 + 1. The simplest way of calculating that is:

LOAD

...

Year(Date) * 12 + Month(Date) as MonthSerialNumber

2. The traditional Month field is a dual field - it holds both the number and the text. You'd have to provide the text in order to make such a comparison in Set Analysis. The function MAX(), however, is a numeric number and returns a number, not a text. For this reasons, Month filters can only be used with Advanced Search filters. For example:

Month = {"=num(Month)>=MAX(Month)-1"}

, still with the limitation of the year-end boundaries that I explained in #1.

However, this problem goes away if you calculate and use MonthSerialNumber, because this field is numeric, not dual. So, the final Set Analysis filter can be:

MonthSerialNumber = {">=MAX(MonthSerialNumber)-2<=Max(MonthSerialNumber)"}

You can learn everything I just explained, and many other QlikView development techniques, from my new book QlikView Your Business. You can also upgrade your Qlik skills at the Masters Summit for Qlik, the most advanced technical conference for QlikView and Qlik Sense.

cheers,

Oleg Troyansky

Re: rolling 3 months not working properly with set analysis...?

sunny_talwar — Thu, 25 Feb 2016 14:54:48 GMT

I like the calculation for MonthSerialNumber. I often think of doing RowNo(), but that would need sorting. But this wouldn't have that problem -> Year(Date) * 12 + Month(Date). Will keep this in mind for later

Re: rolling 3 months not working properly with set analysis...?

Oleg_Troyansky — Thu, 25 Feb 2016 15:32:32 GMT

I also recall seeing an ingenious calculation like:

AutoNumber(MonthYear) as MonthSerialNumber

which also requires sorting, but is very sleek, indeed. I think Stefan suggested it once...

Re: rolling 3 months not working properly with set analysis...?

sunny_talwar — Thu, 25 Feb 2016 16:05:40 GMT

I think doing this AutoNumber() or RowNo() might be easy if we have a MasterCalendar. But without a MasterCalendar I like this new technique that I just learnt from you

Re: rolling 3 months not working properly with set analysis...?

vishalgoud — Thu, 25 Feb 2016 18:51:49 GMT

Hi Oleg,

First of all Thanks a lot for the Best approach , But my bad am unable to get the required one for my situation.

Can you please work on the sample data Provided and share the Qvw.

Please find the attachment in the main post..

Thanks in advance..