Crosstab use Grand Total value in calulation with a calculated member value

[Former Member]

Hi

I have had help with a crosstab to create a calculated member column that sums values from selected columns in the crosstab. The column is called Sum of Conv (see example below)

I would like to create another column to the right hand side of the Sum of Conv column which calculates the percentage converted ie ( the Sum of Conv value divided by the Grand Total value) x 100. This will replicate the calculation currently in the crosstab on a per month basis but as an overall conversion rate for the period selected.

How should I reference these values in a new calculated member? Or will I need another calculation formula in the new calculated member which calculates the Sum of Conv value again to then divide by the Grand Total value?

Thanks

Jon

[abhilash_kumar · Accepted solution]

Hi Jon,

Here's what you need to do:

1) Right-click the header cell of the column 'Sum of Conv' > Calculated Member > Edit Column Value formula > Type in this code:

Cdate(1890,1,1)

2) Right-click the same header cell  > Calculated Member > Insert Column. A new blank column is inserted beside this column.

3) Modify the Calculation Formula for the 'Sum of Conv' formula to:

local numbervar i;

local numbervar summ;

For i := 4 to GetNumColumns Step 4 do

(

    if gridlabelat("@SO_Conv", i) = "Conv" then

            summ := summ + tonumber(GridValueAt(currentRowIndex,i,CurrentSummaryIndex));

);

summ;

4) Right-click one of the zero values (embedded summaries) in the newly added columns > Calculated Member > Edit Calculation formula and use this code:

(GridValueAt(CurrentRowIndex, CurrentColumnindex -1, CurrentSummaryindex)/GetTotalValueFor("My_Date_Field", CurrentSummaryIndex)) * 100

Replace "My_Date_Field" with the database field name that you've used as the first column in the crosstab. Make sure the double-quotes stay as above.

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thank you for your help.

I did the above but the change to the Calculation formula for "Sum of Conv" resulted in zeros.

I changed this formula back to the previous version (ie For i = 3 etc) & this returned the expected values.

Other than that one query your solution above has done exactly what I needed.

Thank you again for your prompt reply & help.

Thanks

Jon

[abhilash_kumar · Accepted solution]

Is the New Percentage column added located to the left beside 'Sum of Conv' or to the extreme right?

Anyway, I'm glad I could help.

-Abhilash

[Former Member · Accepted solution]

It is added immediately alongside the Sum of Conv column on the right hand side (ie starting with Grand Total on left hand side of crosstab then Sum of Conv then % Conv (the new percentage column) - have attached snapshot.

Thanks again for your help.