topic Table Chart Previous Year Month in QlikView

Table Chart Previous Year Month

Thu, 27 Nov 2014 20:31:41 GMT

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.

Re: Table Chart Previous Year Month

Anonymous — Thu, 27 Nov 2014 20:45:04 GMT

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

Re: Table Chart Previous Year Month

Thu, 27 Nov 2014 21:33:50 GMT

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

saurabh5 — Wed, 03 Dec 2014 14:52:54 GMT

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