topic Re: Previous year in 2nd dimensionality in App Development

Previous year in 2nd dimensionality

mike_spada — Mon, 11 Mar 2024 13:53:06 GMT

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

Re: Previous year in 2nd dimensionality

Oleg_Troyansky — Mon, 11 Mar 2024 14:52:53 GMT

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,

Re: Previous year in 2nd dimensionality

mike_spada — Mon, 11 Mar 2024 14:58:17 GMT

Hi Oleg,

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

Mike

Re: Previous year in 2nd dimensionality

jackysins72 — Thu, 14 Mar 2024 20:00:51 GMT

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.