Skip to Content
avatar image
Former Member

Use Total Value in Crosstab Column for Calculation

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 08 at 08:08 PM

    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

    Add comment
    10|10000 characters needed characters exceeded