Counting Issue

[devnaren]

Hi All,

I'm using CRXI and Oracle 9i.

The problem is I am trying to count the no of records of certain event which occurs after another event. Each event is accompanied by date. Lets say event “abc” occur on 27th of Dec and event “xyz” on 31st of Dec. Now the “xyz” event can occur multiple times. So, I need to calculate the no of times event “xyz” happens after event “abc”.

For example data looks like as follows:

ID Event Date

111 xyz 31st Dec
110 efg 30th Dec
109 mno 29th Dec
108 xyz 28th Dec
107 abc 27th Dec
106 efg 26th Dec
105 xyz 25th Dec


Now from the above example I need to calculate the No. Of times event “xyz” occurs after event “abc” which we can verify through Date. So result will be

ID Event Date
111 xyz 31st Dec
108 xyz 28th Dec

Count : 2


How can I get the count of above ID’s or Events?

Thanks

 

[lbass]

I think you could create a formula like this:

//{@abcdate}:
if {table.event} = "abc" then {table.date}

Then go to report->selection formula->GROUP and enter:

{table.date} >= maximum({@abcdate}) and
{table.event} = "xyz"

Then create a running total that does a count of ID, evaluate for each record, reset never, and place this in the report footer. This assumes that you are are not trying to do this at some group level. If you ARE, then add a group condition to the maximum in the group selection formula, and have the running total reset on change of group.

-LB