topic Re: Help comparing data in pivot table in QlikView

Help comparing data in pivot table

Mon, 24 Mar 2014 10:31:31 GMT

Hi, I've been looking along many discussions form this forum for a solution for a very specific problem I've with a pivot table in Qv but I cant find it.

I've a pivot table with sales data from months of different years, and I would like to compare the variation of the sales of each month of each year respect the same month of the previous year.

Actually, my variables are SALER, MONTH, YEAR and SALES, my table has SALER on the rows and YEAR an MONTH on the columns, and I use this:

BEFORE(total sum(SALES),12)

But there is a problem when a filter a year: in that case I can't compare with the previous year.

Does anyone know if theres another way to calculate the sales of the same month of the previous year which works even if there's a filter activated on the year?

Thank you very much in advance!

Re: Help comparing data in pivot table

marcus_sommer — Mon, 24 Mar 2014 13:31:19 GMT

Inter-record-functions like before() aren't suitable, try this:

sum({< Year = {$(=max(Year))}>} VALUE) // max. year according to selection

sum({< Year = {$(=max(Year) - 1)}>} VALUE) // previous year to max. year

- Marcus

Re: Help comparing data in pivot table

Tue, 25 Mar 2014 08:21:39 GMT

Thank you, Marcus!

I tried what you proposed, but the result is just not what I was looking

for. What I'd like to see is the data from the same month of the previous

year ("Last") repeated in the column of each month (marked in red):

YEAR 2013 2014

MONTH 1 2 ... 1 2 ...

NAME Actual Last Actual Last ... Actual Last Actual Last ...

A 4 4 ... 7 4 10 4 ...

B 8 10 ... 3 8 1 10 ...

Thanks again!

JORGE DE LOS MÁRTIRES

Re: Re: Help comparing data in pivot table

marcus_sommer — Tue, 25 Mar 2014 09:28:58 GMT

To compare values between years you shouldn't have year as dimension in your table - have a look on the attachments.

- Marcus

Re: Help comparing data in pivot table

Tue, 25 Mar 2014 10:00:42 GMT

Great!

It's just what I was looking for (I think it's even better, because I think the table I had in my mind would be too wide).

Thank you very much, Marcus!