Event count by first sub-event dimension value?

[robinrouleur]

Hi, this problem has frustrated two of us for most of a day so I'm hoping someone can help?

Our data consists of sub-event records that are grouped into events via an EventID.  Each sub-event has a StaffID field and a TimeStamp field (among others).  What we want to do is to produce a table chart showing the number of times each StaffID appears as the first person involved in an event.  Or, to put it another way, the number of events that were first handled by each person.

We tried creating a straight table with a single dimension StaffID and the  single expression below:

Count(Aggr(FirstSortedValue(StaffID,TimeStamp), EventID))

The thinking being that the Aggr() would create an intermediate table containing the first StaffID against each EventID and then the Count would aggregate over the chart dimensions, ie StaffID.  The result being the count of the number of times each StaffID appeared against an EventID as the first one.

Unfortunately it doesn't work and the results seem unpredictable.  Can anyone help us understand why and/or suggest a modification or alterative approach?

Thanks in advance,

Robin

[#2]

Hi Robin,

it would be easier to troubleshoot if you upload a simple example application

thanks

Joe

[tresesco]

May be like:

Count(If(StaffID= Aggr(FirstSortedValue(StaffID,TimeStamp), EventID),1))

[robinrouleur]

Thanks Tresesco, yes that seems to work though I replaced your Count with Sum.  My main problem turned out to be that I had to duplicate some set analysis that I had in the the FirstSortedValue expression into the Count/Sum expresssion (I left this out of my problem description for simplicity).  Once I had done that both my original expression and your suggestion gave similar results, though not quite the same.  Closer inspection showed yours to be correct.  Why they differ is still a puzzle to me, though I suspect it is due to some "under the bonnet" behaviour that I have not grasped yet.

[robinrouleur · Accepted solution]

It turns out that my approach was wrong.  What I wanted to achieve cannot be done this way as the scope of the Aggr cannot be broader than that of the outer aggregation function.

The solution is to use a calculated dimension:

Aggr(FirstSortedValue(StaffID,TimeStamp), EventID))

with an expression:

Count(DISTINCT EventID)

There is some useful discussion on this in the standard QlikView Help under:

"Nested Aggregations and Related Issues"