I want to calculate the difference from present to previous week in Spotfire cross table. Difference I want to calculate is the cumulative difference between present to previous week (Newly Added in week).
Here is my example data
and the expected output table is
Please help me to solve to create this in Spotfire cross table
I cannot find the exact solution you want using a cross table, but there are possible alternatives.
1 - use your existing Cross Table (Horizontal: Week, Vertical: ID and Status) to show the Totals (Grand total for columns) and then you get your Newly Added in week row (although I don't understand why your first cell is null, as judging from the Cumulative Sum I thought it would be 4).
2 - add a Waterfall Chart with [Week] on the X axis. You can colour by [ID] or [Status] or both.
This reacts to filtering.
You could go via calculated columns instead, but they would not react to filters:
1 - first calculate [Weekly Count] as
Count() over ([Week])
2 - then calculate [Cumulative Sum] as
Sum(case when Rank(rowid(),[Week])=1 then [Weekly count] end) OVER (AllPrevious([Week]))