Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
vishalgoud
Contributor III

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

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.

6 Replies
Highlighted

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

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)

Highlighted
MVP & Luminary
MVP & Luminary

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

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

Highlighted

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

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

Highlighted
MVP & Luminary
MVP & Luminary

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

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...

Highlighted

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

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

Highlighted
vishalgoud
Contributor III

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

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..