Help comparing data in pivot table

[#1]

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!

[marcus_sommer]

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

[#3]

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

[marcus_sommer · Accepted solution]

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

- Marcus

[#5]

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!