PY Value as calcultated dimension

[#1]

Hi,

I have a pivot table with detail for Code Mat on vertical and Month on horizontal (see the attachment). The user is forced to select at least one value for the Year, Country and Currency.
I need to calculate the Quantity for the previous year based on the user selection: If he selects 2013 he'll visualize the Quantity for 2012 and so on. This indicator will be a calculated dimension. I can't get the correct formula.

I tried to create it but it seems like it gets the total quantity for all the years and when I change the year the value doesn't change:

=num(aggr(Sum({<YEAR= {"$(=Max(YEAR)-1)"},YEAR=>} QUANTITY) , CODE_MAT), '#,##0.##', '.' , ',' )

Can you help me with this, please?

[israrkhan]

hi,

Try this:

=num(aggr(Sum({<YEAR= {"$(=Max(YEAR)-1)"}} QUANTITY) , CODE_MAT), '#,##0.##', '.' , ',' )

[#3]

It doesn't work, using this formula all the values disappeared.

[israrkhan]

can you please upload sample app.

[#5]

Here it is a sample application.

Thank you

[israrkhan]

i am unable to open the document,

getting this error "Failed to open document"

never faced it before.

[christian77]

Hi Cristina:

try the next.

make the variable in your var panel: vPreviousYear=max(YEAR)-1

num(aggr(Sum({<YEAR= {$(vPreviousYear)}>} QUANTITY) , CODE_MAT), '#,##0.##', '.' , ',' )
it should work.

also you have  }}  at the end instead of }>}

Since YEAR is a number, you don't need quotes. Anyway, I always use single quotes instead of doubles.

Good luck.

[christian77]

Besides, calling the same field twice in your set is weird. You can call only once a field, as far as I know.

[christian77]

First open your application, then drag the document inside.

Otherwise it can be a question of licenses.

[#10]

Hi,

It still doesn't work. There is no value if I use this formula. I was thinking maybe is something related to the user selection. If I use my formula at least I can see some data even if the values don't chance when I change the year, but do change when I select another country or currency.