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
