count once within a time interval

[damian_spyra]

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...

[flipside]

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.

[damian_spyra]

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...

[flipside · Accepted solution]

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

[damian_spyra]

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?

[damian_spyra]

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!