Discussion Board for collaboration related to Creating Analytics for QlikView.
I need to create a expression in a pivot table that calculates the month previous year amount, but I need this to work when you select various and different Year_Months:
Selected: 201410, 201401
YearMonth 201401 201301 201410 201310
Product A 10000 8000 5000 2700
and has to be dynamic...
Thanks in advance,
An as of table may well be what you need, have a look at this blog post by Oleg Troyansky
QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies
I can do this, with an extra previous year amount column in the facts table, but I would like to do it with set analysis...
Year & MonthNo as YearMonthNo,
Year &'-'& MonthNo as YearMonthVal,
AutoNumber (Year & MonthNo, 'MonthID') as MonthID,
'Q' & Ceil (MonthNo/3) AS Quarter;
LOAD Date($(vMinDate) + IterNo() -1) AS [YearMonth] ,
Year($(vMinDate) + IterNo() -1) AS Year,
Month($(vMinDate) + IterNo() -1) AS Month,
Num(Month($(vMinDate) + IterNo() -1)) AS MonthNo
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate) ;
use the above calender table and then use MonthID in the set analysis
curent month; max(MonthID)
previous month: max(MonthID)-1