Cross table difference between columns

[Daniel Yugueros]

I believe I have the same question as the following two:

 

https://community.spotfire.com/questions/difference-between-two-columns-cross-table

https://community.spotfire.com/questions/how-show-difference-between-two-columns-cross-table

 

however the answers provided are insatisfactory (in one case it says it is not possible); also I was hoping there may be some news since the answers were provided... I want to calculate the difference between 2 (or more) columns in a cross-table, each column being a SUM aggregation of a data table column.

To note the actual values to be included in the columns are based on marking from another visualization so there is no fixed name for them. I tried with this and it seemed to work although it showed one column with all 0 values corresponding to computing the first column minus itself.

But then I now see sometimes values different than 0 in the column where I expect 0 ...

Sum([MyColumn]) THEN [Value] - First([Value]) OVER (Intersect(Parent([Axis.Columns])))If I try the following it only works for 2 columns ...

Sum([MyColumn]) THEN [Value] - First([Value]) OVER (Previous([Axis.Columns]))If I try the following the Grand total row doesn't seem to work...

Sum([MyColumn]) THEN [Value] - First([Value]) OVER (AllPrevious([Axis.Columns]))

[Khushboo Rabadia]

Can you provide the sample dxp showing your use case and expected output.

[Mark.Anderson]

Your first expression worked for my application.  I wanted to subtract two columns of annual averages in a cross table. I changed First() to Last() in my case.

Sum([MyColumn])/12 THEN [Value] - Last([Value]) OVER (Intersect(Parent([Axis.Columns])))

Now if only I can find a way to suppress the first blank difference column...

[David Boot-Olazabal]

Hi Mark,

Can you share a screenshot so we know what your visualization looks like and what you want to suppress?

Kind regards,

David