Use Total Value in Crosstab Column for Calculation

[Former Member]

Hi

I have a crosstab that provides columns by grouped date & several rows of data grouped by category.

I would like to take the Total value for each Date column & use it in a calculation to provide a percentage of the total for each row

Eg

Mar Apr May

Total 100 150 120

Cat A 50 50 60

Cat A % 50% 33% 50%

Cat B 50 100 60

Cat B % 50% 67% 50%

Please could anyone help in how I reference the Total Value in the Column for use in a calculation for an Embedded Summary (if that's the correct method to use?!).

Thanks

Jon

[abhilash_kumar]

Hi Jonathan,

Here's what you need to do:

1. Right-click one of the summary cells > Embedded Summary > Insert Embedded Summary.

2. A new cell with the title "Edit this formula" will be inserted on all rows.

3. Right-click one of the valules in this new cell > Embedded Summary > Edit Calculation Formula > Type in the number zero:

0;

Now, all rows will show up as zero for this Embedded Summary.

4. Right-click one of the values in the "Total" row > Format Field > Common tab > Click the formula button beside "Suppress" and use this code:

shared numbervar array x; 
local numbervar i; 
for i := 1 to GetNumColumns-1 do 
( 
   redim preserve x[i]; 
   x[i] := tonumber(GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex)); 
); 
false;

4. Next, right-click one of the cells in the Embedded Summary > Format Field > Common tab > Click the formula button beside "Display String" and use this code:

shared numbervar array x; 
totext(gridvalueat(CurrentRowIndex, CurrentColumnIndex, 0)%x[CurrentColumnIndex],'#') & '%';

-Abhilash