variance calculation

[Former Member]

Hi,

i am trying to do an variance calculation in a crystal cross tab but not too sure what the syntax should be.

In the cross tab I have the following columns. If I want to do a variance between Cat 2 and Cat1 what should the formaula be? i am using a new column inserted as a calculated member. I was thinking of something like

Amount where Category = "Cat 2" - Amount where Category = "Cat 1"

However, I cant see a where command?

Cat 1 Full Year	Cat 2 Full Year	Cat 3 Full Year	Variance Cat 2 & Cat 1 (FULL YEAR)
101	104	110	3
102	105	111	3
103	106	112	3

Thanks

[abhilash_kumar · Accepted solution]

Hi Leo,

Here's what you need to do:

1) You said you've already inserted the Calculated Member column right? Now right-click one of the cells in the Calculated Member and select Calculated Member > Edit Calculation Formula and use this code:

GridValueAt(currentRowIndex,GetColumnPathIndexOf("Cat 2 Full Year"),CurrentSummaryIndex) -

GridValueAt(CurrentRowIndex,GetColumnPathIndexOf("Cat 1 Full Year"),CurrentSummaryIndex)

If that doesn't work, just use this:

GridValueAt(currentRowIndex,currentColumnIndex - 2,CurrentSummaryIndex) -

GridValueAt(CurrentRowIndex,currentColumnIndex - 3,CurrentSummaryIndex)

-Abhilash