topic Get previous year in pivot table in QlikView

Get previous year in pivot table

ramonarcusa36 — Mon, 13 Feb 2012 18:00:25 GMT

Hi you all.

I have built a pivot table with three dimensions: Product, Year, Month. I'd like to get a table with one row for each product, and one column for each year-month combination.

I need to write an expression returning the previous year sales (for every month). That is, I need to perform my calculations as if Year field wasn't present in the table.

If I try something as simple as this: SUM({$<Year= {2011}>} Sales), I just get a valid data in the 2011 context.

For example (look at that 1500: it's the previous year sales for January 2012):

	2011	2011	2011	2011	2012	2012	2012	2012
Product	Jan	Jan	Feb	Feb	Jan	Jan	Feb	Feb
	Prev. Year	Current Year	Prev.	Current	Prev.	Current	Prev.	Current
A	-	1500	-	2000	1500	4000	2000	7515
B	-	715	-	1555	715	7017	1555	650

How can I ask Qlikview to forget he's under the influence of the current year so he can give me the previous year sales in that orange cells...

Thanks in advance!

Re: Get previous year in pivot table

swuehl — Mon, 13 Feb 2012 18:11:14 GMT

Try something like

=aggr(above(sum(Value)), Product, Month, Year)

as expression for Prev (The order of dimensions is important here!)

Regards

Stefan

edit: attached sample file

Get previous year in pivot table

ramonarcusa36 — Tue, 14 Feb 2012 08:34:25 GMT

Thank you very much Stefan, you have showed me an interesting usage of the Above function that I didn't know. This will be very helpful for me in this case and in future ones.

But... (there's always a "but"...), let's suppose you add a listbox for the Year field, in your example. Then, select 2012 and 2013, and disable the "Current" expression (I just want to show one expression, performing an operation with current and previous year). Now, you just get data for 2013. I think that happens because 2011 is not selected so there is no previous year for 2012. Is there any thing we can do to solve it?

Thank you again!

Get previous year in pivot table

Jason_Michaelides — Tue, 14 Feb 2012 08:39:40 GMT

This is taken straight from the F1 help on Set Analysis - give it a try:

sum( {$<Year = {$(#=Only(Year)-1)}>} Sales )

Hope this helps,

Jason

Get previous year in pivot table

ramonarcusa36 — Tue, 14 Feb 2012 09:32:41 GMT

Thanks for your quick reply, Jason.

I've tried your expression in the swuehl example (previous answer to yours) and it doesn't work. Is it maybe because I can't go the Only way in a pivot table? Is the Above function the only way to get the previous year in a pivot table(having, obviously, the Year field as a dimension in the table; if Year wasn't there, there wouldn't be a problem)?

So far, I'm able to get the previous year (with Above function), but if I have 2011-2012-2013, I need to make a selection of all three years to work properly. I don't want to be forced to select 2011.

Come on guys, we're close!

Re: Get previous year in pivot table

swuehl — Tue, 14 Feb 2012 10:14:05 GMT

Ok, try

=aggr(above(sum({<Year=>} Value)), Product, Month, Year)

edit:

attached sample file

Re: Get previous year in pivot table

v_iyyappan — Tue, 14 Feb 2012 10:37:23 GMT

Hi,

use expression like this.Its help for u.

=Sum({<Year=, NumDate={'>=$(=(Num(YearStart(AddYears((selectedDate),-1)))))<=$(=(Num(AddYears((selectedDate),-1))))'}>}Sales)

Where Num(Datefield) AS NumDate

selectedDate=MakeDate(Max(Year),Max(Month),Max(Day))

Regards,

Iyyappan.

Get previous year in pivot table

ramonarcusa36 — Tue, 14 Feb 2012 18:02:58 GMT

Fine, writing "Year=" has solved that point. Now I can get data from previous year even if it isn't selected in my listbox.

I have added an "If" with Dimensionality and SecondaryDimensionality functions to make calculations at different levels depending on the cell I am (I have several partial sums).

It looks like everything it's working great.

Thank you all!