TIBCO Spotfire

Hi, 

I have data from 2 sources which I have appended together into a single Excel spreadsheet.  Here is a simplified version of the data:

Cost center | Source | Amount | ...

CC001 | Actual | 100 | ...

CC001 | Forecast | 80 | ...

I'm using the cross tab to display this data as follows and want to calculate the variance for each cost center (as shown below)

                Actual         Forecast      Variance

CC001          100                  80               -20

It sounds easy enough but I can't figure out how to do this.  I've tried to create a Variance column and tried custom expressions but just can't get it to work.    In excel, this is easy to do with a pivot and then manually create a column outside the pivot to show the variance but I can't seem to make it work in Spotfire.  Any guidance would be much appreciated.  Thanks!

 The easiest way is to do the same as in Excel: create a pivoted table (File Add Data Table, from existing table, with PIVOT!)

And insert a calculated column to the  resulting Table where you do A-B

 

Christof

That fix worked thanks.  If i were inclined to do so, is there a way to do this on the fly in a cross table without the extra step of creating the pivot table? 

Hello,

yes there is.

Please put the cost center on the y axis and select `(column names)` on the x axis.

Then create the following columns

sum(if([Source]="Actual",[Value],0)) as [Actual], sum(if([Source]="Forecast",[Value],0)) as [Forecast], sum(if([Source]="Actual",[Value],0))-sum(if([Source]="Forecast",[Value],0)) as [Variance]

This should do the trick.

Kind regards, Andre

Hi Guys, I´m stucked with the same problem: Sum(if([MONTH]="6",[IMPORTE],0)) as [June], Sum(if([MÊS]="7",[IMPORTE],0)) as [July], Sum(if([MONTH]="6",[IMPORTE],0)) - Sum(if([MONTH]="7",[IMPORTE],0)) as [Difference] I have the amount of money expended on month "6" and month "7" and I´ll try to calculate the difference between them, but with this formula I get "Please specify an aggregation method" on the Cross Table component I had researched about the way out of this and many says that the problem resolves with the inclusion os the First() aggregation,... but still trying to include it without success, could you please tell me how did you resolved or help me in some way? Thanks!

Hello Cesar,

2 things to check maybe:

1) if your [Month] column is an integer or real, please remove the " ", because that will make the formula fail
2) Please check your formula for July, since it has [MES] in stead of month

If [Month] is a column for integers, this should work:

Sum(if([MONTH]=6,[IMPORTE],0)) as [June], Sum(if([Month]=7,[IMPORTE],0)) as [July], Sum(if([MONTH]=6,[IMPORTE],0)) - Sum(if([MONTH]=7,[IMPORTE],0)) as [Difference]

You must be very close.

Kind regards, Andre

 

Perfect! the problem were the integer values, Thanks a lot!