Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
To compare values between years you shouldn't have year as dimension in your table - have a look on the attachments.
- Marcus
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
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
To compare values between years you shouldn't have year as dimension in your table - have a look on the attachments.
- Marcus
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!