3 Replies Latest reply: Dec 3, 2014 9:52 AM by saurabh singh

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

Francisco.

• ###### Re: Table Chart Previous Year Month

An as of table may well be what you need, have a look at this blog post by troyansky

QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies

• ###### 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

• ###### Re: Table Chart Previous Year Month

CALENDER:

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