Jump to content

Cross table difference between columns


Daniel Yugueros

Recommended Posts

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

Link to comment
Share on other sites

  • 2 months later...
  • 5 years later...

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

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