Cross Tab Percentage

[eebabe]

Ok. Looks like I'm having about the same problem as some people but not quite. I am using CR9. Here is my data:

Name Jan Feb Mar Apr
EMT1 Ttl Runs 4 18 19 25
Ttl Late 0 1 6 2
% Compliance

I'm not sure how to do that because % compliance is 1-(ttl Late/Ttl Runs) ie. for Jan 100%, Feb - 94%, Mar - 68% and Apr - 95% (If this is done on excel). So what I did was sum ttl late as show percentage of row which resulted in way off like mar as 12% and apr 4% (if I were to just calculate ttl late/ttl Runs)

Maybe someone can help me out. Thanks.

 

[lbass]

Assuming you have a column based on date on change of month, a row based name, and currently two summaries, add a formula {@0} for your third summary just to act as a holder field:

whilereadingrecords;
0

Then in preview mode, select an inner cell for Ttl Runs->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar runs := currentfieldvalue;
false

Repeat for Ttl Late, but enter a different formula in the suppress formula area:

whileprintingrecords;
numbervar late := currentfieldvalue;
false

Then select {@0}->right click->format field->DISPLAY STRING->x+2 and enter:
whileprintingrecords;
numbervar runs;
numbervar late;
if runs = 0 then
"--" else
totext(100-(late%runs),1)+"%" //1 for 1 decimal

-LB

 

[GJParker]

Hi

Lindas technique works fine as long as you don't intend to export the data using the 'excel data only' format. I have found that this export method ignores the display string value and exports the field value so your compliance row will export zeros.

An alternative method is to group your report by the same date field you used in the crosstab columns and create a formula to calculate the compliance value i.e.

If Sum({ttl.runs},date group) <> 0 Then
    Sum({ttl.late}, date group) / Sum({ttl.runs},date group) * 100
Else
    0

Finally add this field into your crosstab and set the summary type to average.

HTH

Gary Parker
MIS Data Analyst
Manchester, England