Adding or Subtracting categories

[Marijn Bax]

Dear Community,

 

I have a column called CATEGORY. In this column I have 3 values

1. Actual

2. FX_DIFF

3 RFF 2019.07

I have created a pivot table that has CATEGORY in the columns, PROFIT CENTER in the Rows and the value dimension is called AMOUNT.

 

I would like to add in an additional column called difference at the end of the pivot table that does Actual - FX_DIFF - RFF 2019.07= Difference

[Neil Kanungo 2]

Hi marijn.bax,

You are using a Cross Table Is your original data something this:

 

 

 

CATEGORY

PROFIT_CTR

AMOUNT

 

 

Actual

R39

26,232

 

 

FX_DIFF

R39

0

 

 

RFF 2019.07

R39

5

 

 

Actual

R56

22,122

 

 

FX_DIFF

R56

0

 

 

RFF 2019.07

R56

1,205

 

 

 

I think what you actually want to do is first use a "Pivot" Transformation then an OVER function...

Pivot Transformation - Looks like you are using Spotfire 7.x... from menu go to "Insert > Transformations"

 

Now your data table will look like the format you have in your cross table. Note I used an "avg" aggregation for "Amount". Since I only expect there to be one "Amount" per "Category" and "PROFIT_CTR", the Avg aggregation will be fine here.

Next, with a Calculated Column create this OVER function and name the new column "Difference":

Avg([Actual] - [FX_DIFF] - [RFF 2019.07]) OVER [PROFIT_CTR]And you should be done!

 

CAVEAT:

If you have other charts and analysis built on your original data table format, this Pivot Transformation will break all of them.

So if that's the case, you could instead add this data table to your dxp twice. The most efficient way to do this is Add it once, then go to "Add Data Table" again but this time choose "From Current Analysis". Then with this new data table, Add the Pivot Transformation and OVER function as described above.

Okay, so now you have two separate data tables... how do you get them to interact together Go to "Data Table Properties" and "Relations" and relate your two tables by a common column, such as PROFIT_CTR.

**Note this process is all easier in Spotfire 10.x, but looks like you were using Spotfire 7.x

[Marijn Bax]

Hi Neil, 

 

 

 

Thanks so much for your help. 

 

Indeed I'm using spotfire 7.x (we will shortly be migrating to 10.x) 

 

Indeed that is what my data looks like. 

 

When I try do do what you are suggesting I do not seem to get the same result. Somehow the columns only show actual. (see my print screen) 

 

I do have a lot of other columns. Should I list them all 

 

 

When I click ok Ok after this step and the data recalculated I get the error message that it can't find the CATEGORY tabel any more and my report (pivot table) breaks. 

 

Appologies I am quite new to spotfire. 

 

 

 

Kr, Marijn