Jump to content

Calculating cumulative sum in Spotfire and sorting columns in Cross table

Marko nidari

Recommended Posts

I need to calculate cumulative sum and cumulative percentagein cross table. I can use over function with allprevious and it works fine if no sorting is used. After using sort Spotfire does the sorting as expected but does not recalculate the cumulative sums. It rembers the row value from its natural row sort.

Any ideas how to solve this

Link to comment
Share on other sites

There is currently an enhancement request in for this feature so that sorting will effect Cross Table expressions like:


Sum([myData]) over (AllPrevious([Axis.Rows]))


So I actually don't know of a way to do this directly in a cross table (its an interesting question though). But you might want to open a ticket with TIBCO at https://support.tibco.com so that they can add your request on to that enhancement too.

Link to comment
Share on other sites

I think the only way to do that would be to create a couple calculated columns to create your new sorted index and then reference that in the cross table. This would be limited since it would NOT be updated based on filtering though.


For example:



Sum([myData]) OVER ([myVerticalAxisCategory])







Then in your Cross Table, your cell values expression could be:


Sum([myData]) OVER (AllPrevious([sortingIndex]))

Link to comment
Share on other sites

  • 3 years later...

Calculated columns:

Create a calculated column[sortingIndex]:

DenseRank(Sum([MyData]) over ([VerticalCategory]),"desc")

Create another calculated column, [cum_sum]:

Sum([MyData]) over (AllPrevious([sortingIndex]))

Create the last calculated column [total_sum]:

Sum([MyData]) over (All([VerticalCategory]))


Into the crosstable axes:

Horizontal: (Column Names)

Vertical: [VerticalCategory]

Cell Values:First([cum_sum]) / First([totales])

Link to comment
Share on other sites

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...