Jump to content

Cross Table - Difference Over Function

Kai Lattmann

Recommended Posts


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



Link to comment
Share on other sites

  • 1 month later...

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



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

Link to comment
Share on other sites

  • 2 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...