Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a Variable that sums up the sales. In a pivot table, I have the column with sales, and another one with budget. I would like to bring the sales from last year in the same table as well by using =count({<[Current Year]={$(=Max([Current Year])-1)}>} [Variable]) but this does not return the correct number. Any suggestions?
How is your variable looks like?
May be this
=count({<[Current Year]={'$(=Max([Current Year])-1)'}>} [Variable])
or
=count({<[Current Year]={'$(=AddYears(Max([Current Year]),-1))'}>} [Variable])
the format is =sum({<year={$(=max(year))}>}sales)
Thank you for the suggestions but none of them worked actually. Must be a result of the complex variance. I have decided to go for a different option. I created a variable for last year and re-calculated the sales based on this variable (query was slightly amended).
Hello Petra,
I almost never write set analysis for date fields. The calendar of the qvc library has the set analysis variables baked in.
It has a highly recommended calendar generator.
,Hannah
PS: Don't forget to mark questions as helpfull or correct .