Cross Table - Difference Over Function

[Kai Lattmann]

Hello,

ich have a hugs problem to solve. I have a data table and i want to get a cross table from it. I attaching a file, where you can see and following my minds.

 

 

I want to the the difference between the month. If the previous month is not in the Data Table, then must be the difference the starting amount, if the previous mounth in the table, then must be the difference. The Over-Function/Difference need for Paid and Recovered.

 

I had mark the problem with a screenshot in the file. Hope I can explain clearly.

Hope, anyone can help me to solve it.

Kind regards and THX

 

Kai

[Khushboo Rabadia]

Your can use SN() to replace null values with 0 like

Sum([Paid])

THEN

SN([Value],0) -

SN(Integer(Split(SN(Concatenate([Value]) OVER (AllPrevious([Axis.Columns])),""),",",-2)),0)This will solve all missing values issues and solve this issue i.e. if previous value is available consider it for subtraction otherwise look for last previous available value.

AllPrevious helps to get all the previous values, so concatenating those values and retrieving the last second value will always get the lastprevious value.

Regarding incorrect value in May month, in visualization properties > Totals > Select Grand Total for columns > Calculate Totals > Select "As sum of cell values" that will show 0 instead of -6000

[Kai Lattmann]

THX for your help, but i fixed it with this:

 

SN(sum([Payment Total LC]),0) - SN(Sum([Payment Total LC]) over (Previous([Axis.Columns])),0) as [Payouts]

 

Can you accept or confirm it ... Thx for your help

[Khushboo Rabadia]

Yes that works as well when previous month is available. Say there is no march data then april month will not use Feb month data, it will have 0 for previous month. As per the requirement, "If the previous month is not in the Data Table, then must be the difference the starting amount" that was needed i guess.