Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to replicate this pivot
I can calculate previous year amounts for dimensionality()=1, with this formula:
if(Year= $(vSelectedYear),
Sum(total {<Year= {"$(=$(vPreviousYear))"}>} Amount)
and so on.
repeating for different variables (I can have only 6 years to investigate). That's ok for me.
But I need to do the same with Category field too, that I can see when I expand the pivot, and I cannot.
I used formulas like these:
Sum({1<Year = {"$(=$(vPreviousYear))"}>} Amount)
Sum(aggr(sum({<Year={"$(=$(vPreviousYear))"}>} Amount), Category))
with no success.
Can anyone help me please?
Thanks in advance.
Mike
Hi Mike,
The reason your first formula worked, is the use of the TOTAL qualifier - it allowed you to sum up amounts for previous year, even though they are not associated with the Year in the Dimension.
I believe you should do something similar for the total by Category:
if(Year= $(vSelectedYear),
Sum(total <Category> {<Year= {"$(=$(vPreviousYear))"}>} Amount)
As an alternative, you could implement the "As of Date" table with conditional flags, which would offer a much more scalable solution, but it involves a lot more work.
Cheers,
Hi Mike,
The reason your first formula worked, is the use of the TOTAL qualifier - it allowed you to sum up amounts for previous year, even though they are not associated with the Year in the Dimension.
I believe you should do something similar for the total by Category:
if(Year= $(vSelectedYear),
Sum(total <Category> {<Year= {"$(=$(vPreviousYear))"}>} Amount)
As an alternative, you could implement the "As of Date" table with conditional flags, which would offer a much more scalable solution, but it involves a lot more work.
Cheers,
Hi Oleg,
the formula you provided works perfectly! Thank you very much!
Mike
Have you tried using the aggr(find lost children) function with the Category field? It might help in replicating the pivot structure for previous years.