How to use previous()

[kate8]

Hi,
I have a report which grouped records by manager’s ID. In manager’s ID group, there are rep name and other info. Each rep has event id. Each event is only displayed once. Now I need to calculate the percentage in each manager‘s group of finished events on time.
When I check if the event finish on time or not, I compare dueTime with finishTime. If finishTime-dueTime < 1 then count it as on time. I create a formular OntimeCount :
If (finishTime-dueTime )< 1 then 1
Else 0
Now the issue is, for some rep, under one event id, has more than one records, so if a rep has one event id but 2 records under that id, my counter will count 2, but one event id only can be considered as one. For example:
Manager1
Rep 1 record1 – not ontime --0
Rep 2 record1 – ontime –1
Record2- ontime -1
Total rep id is 2, my on time count is 2, it should only be 1, so I got 100% on time, actually should be 50% for manager1. I don’t want to count record2.
I tried to use previous() function and do:
if( previous({{table.event_id})<> {table.event_id}) & finishTime-dueTime < 1)
then 1
else 0
There is no error for formula, but got error on the report “A summary has been specified on a non-recurring field”.
Does it mean I can’t use previous() on event_id? How can I get my percentage corrected number base on this requirement?

Thank you so much for any helps and suggestions!!!

 

[lbass]

Create a formula {@null} by opening and saving a new formula without entering anything. Then change your formula to:

if finishTime-dueTime < 1 then
{table.eventID} Else
tonumber({@null})

Remove the tonumber if eventID is a string. Then you can place this in the detail section and insert a distinctcount on it.

-LB

 

[kate8]

lbass,

Thanks a lot!! I got it.