Calculating cumulative sum in Spotfire and sorting columns in Cross table

[Marko nidari]

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

[Sean Riley]

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.

[Sean Riley]

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:

[Aggregation]

 

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

 

[sortingIndex]

 

Rank([Aggregation])

 

 

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

 

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

[Marko nidari]

Thank you Sean. We also tested the approach with calculated columns and it works as you described. Works well until you need to filter data.

[Alejandra Taborda]

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])