topic count once within a time interval in QlikView

count once within a time interval

damian_spyra — Mon, 19 Mar 2012 12:50:21 GMT

Hi all,

i have a counting problem.

I want to count events within given time interval. One single event can occur repeatedly. So they will be counted distinct for (let say) every 30 days from the initial date when this particular event took place for the first time. The challenge ist to create the dynamic time grid for 30 days, which begins at a given date from an event.

I'm trying to use set analysis in order to dynamic change the interval length (for example fo 30, 60, 90 days).

Thank's for any help...

Re: count once within a time interval

flipside — Mon, 19 Mar 2012 14:18:20 GMT

Hi,

Try this expression, it seems to work for my small test dataset ...

=count(distinct class(EventDay-StartEventCount,vPeriod))

My test data is like this ...

(Data)

LOAD * INLINE [

EventId, EventDay

101, 01/01/2012

102, 02/01/2012

103, 03/01/2012

... and StartEventCount is calculated in the load script as ...

left join (Data)

LOAD

EventId,

min(EventDay) as StartEventCount

resident Data Group By EventId;

vPeriod is the Class interval variable.

flipside

PS The expression is calculated over dimension EventId.

count once within a time interval

damian_spyra — Tue, 20 Mar 2012 08:54:57 GMT

Hi flipside,

thank you for your idea!

After some tests I recognized next trouble.

In my test example i used a threshold with 10 days distance (vPeriod). "Init Date" is your "StartEventDistance" and "Date" is equal to "EventDay". We have one Event on 04/11/2011 and the next on 13/11/2011. There is no 10 days between, but the class function create here two instances: 40<=x<50 and 50<=x<60. As a result this Event will be counted twice instead of only once.

I'm stuck...

count once within a time interval

flipside — Tue, 20 Mar 2012 10:08:15 GMT

Hi dorner,

The formula is counting in batches of 10, so 0-9, 10-19, 20-29 etc, so the example you quote is technically correct as you have a date in the 40-49 band, and one in the 50-59 band.

What you now require is the ability to test the Days past with the row above and set the count to zero if the Days past difference is within the threshold period. I would have thought you could have used the Above function, but I cannot get it to work.

flipside

count once within a time interval

damian_spyra — Wed, 21 Mar 2012 15:53:28 GMT

Hi flipside,

yes, you are right.

I obtain the DaysPast in load script now. So I don't need the table anymore.

But how to compute the distance between them?

It seems to be something like inter record deviation?

count once within a time interval

damian_spyra — Fri, 23 Mar 2012 07:33:07 GMT

Hi flipside,

i've done this in script:

ST_EVENT:

LOAD

Event ID,

Date,

Init Date,

Date - Init Date as DaysPast,

...

FROM SourceTable GROUP BY Event ID, ORDER BY Date;

and then,

ST_EVENT_FINAL:

LOAD

DaysPast - Previous(DaysPast) as Threshold,

...

RESIDENT ST_EVENT;

After that i can use Threshold field in Set Analysis. Something like:

Count({$< Threshold = {">=$(vCSThreshold)", 0} >} distinct EVENT)

Thank you for your idea!