topic Event count by first sub-event dimension value? in QlikView

Event count by first sub-event dimension value?

robinrouleur — Tue, 21 Jul 2015 08:28:16 GMT

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

Re: Event count by first sub-event dimension value?

Tue, 21 Jul 2015 08:36:32 GMT

Hi Robin,

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

thanks

Joe

Re: Event count by first sub-event dimension value?

tresB — Tue, 21 Jul 2015 09:01:46 GMT

May be like:

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

Re: Event count by first sub-event dimension value?

robinrouleur — Tue, 04 Aug 2015 02:39:29 GMT

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.

Re: Event count by first sub-event dimension value?

robinrouleur — Wed, 12 Aug 2015 01:21:07 GMT

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"