Compliance percentages in a cross-tab

Friday 15 June 2012 @ 6:47 am

I was asked to create a cross-tab that showed a percentage of assignments completed on time.  The row was a technician name and the column was the month.  After working through several more complex ways to do this, I found a simpler method.  I wrote a formula that said:

If {data.Ontime} = 'Y' then 100 else 0

I then set up the cross-tab with the date as the column field (option set “for each month”) and the tech name as the row field.  I used my formula as the summary field and used “average” as my summary.  This gave me a quick cross-tab showing each technician’s monthly on-time percentage.

There was only one complaint.  The cross-tab did not distinguish between a month with no assignments for that tech, and a month with assignments that were all done late by that tech.  Both would show up as 0%.  So I tweaked the on-time formula to be:

If {data.Ontime} = 'Y' then 100 else 0.0000000001

With this formula, a bunch of late records would average out to a very small number that would still display as a zero.  But, a month with no assignments at all would have a cell that was a true zero.  I then formatted the cells of the Xtab (Number tab, customize button)  to “suppress if zero”.  Now if the value showed a visible zero (actually a very small number rounding to zero) it means records exists but none were on time.  However if there were no calls at all in that month for that tech, then the cell would have a true zero, and therefore would be suppressed and show as an empty cell.

Of course, if NONE of the techs had a call in a particular month then that entire column for that month would not appear in the cross-tab.  It will only show a blank month cell when one tech has no records in a month, but that month has activity from at least one other tech.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro