Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Table Chart Previous Year Month

Hi,

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:

Example:

Selected: 201410, 201401

Table:

YearMonth 201401 201301  201410 201310

Product A   10000     8000     5000     2700

and has to be dynamic...

Thanks in advance,

Francisco.

Tags (1)
3 Replies

Re: Table Chart Previous Year Month

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

Not applicable

Re: Table Chart Previous Year Month

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


Regards,


Francisco

saurabh5
Contributor II

Re: Table Chart Previous Year Month

CALENDER:

LOAD [YearMonth],

     Year,

     Month,

     MonthNo,

     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

Community Browser