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
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