Calc on Pivot Table

[lucasfioravanso]

Hi guys, how r u?

I need to create this visualization:

In my data model I don't have the column novo_sem_zeramento_mais_novo. How can I do  this? After month 05 I need to consider this calc:

06/01/22: saldo_atual_balanco (month 5) 10,159,969.80 + mes_balancete(monht 6) 39,926.00 = novo_sem_zeramento_mais_novo 10,199,895.80

07/01/22 = novo_sem_zeramento_mais_novo (month 6) 10,199,895.80 + mes_balancete(monht 7) 48,251.00 = novo_sem_zeramento_mais_novo (month 7) 10,248,146.80

08/01/22 = novo_sem_zeramento_mais_novo (month 7) 10,248,146.80 + mes_balancete(monht 8 )436,245.00 = novo_sem_zeramento_mais_novo (month 8 )10,684,391.80

and go on... 

Thank you!!

[Oleg_Troyansky · Accepted solution]

Hi Lucas,

I believe you can solve this problem using the function Above() - that will allow you to fetch the balance from the previous line in the table. 

Keep in mind that the function Above() returns NULL in the first row of each group of dimension values. Adding anything to NULL remains a NULL, so instead of a simple addition, you should use the function RangeSum(), something like this:

RangeSum(sum(Transaction), above(sum(Balance)))

There is one more risk though - a user can sort the table differently, and that will break the logic of the Above() function. To overcome this issue, you can combine your formula with the function AGGR() :

AGGR(

RangeSum(sum(Transaction), above(sum(Balance))

, Date)

This way, your data will be always aggregated in the correct order by Date.

Let me invite you to my session on Set Analysis and AGGR() that will take place online on March 1st. I will be going through several advanced uses of Set Analysis and AGGR, and one of my examples covers exactly this situation. Register here.