Previous year in 2nd dimensionality

[mike_spada]

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

[Oleg_Troyansky · Accepted solution]

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,

[mike_spada]

Hi Oleg,

the formula you provided works perfectly! Thank you very much!

Mike

[jackysins72]

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.