0

# Use Total Value in Crosstab Column for Calculation

Feb 08 at 04:09 PM

22

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
Feb 08 at 08:08 PM
0

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

Share