5 Replies Latest reply: Mar 23, 2012 3:33 AM by Damian Spyra RSS

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

        • Re: count once within a time interval
          Dave Riley



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


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


          My test data is like this ...



          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)



          min(EventDay) as StartEventCount

          resident Data Group By EventId;


          vPeriod is the Class interval variable.






          PS The expression is calculated over dimension EventId.

            • count once within a time interval
              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...

                • count once within a time interval
                  Dave Riley

                  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.



                    • count once within a time interval
                      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?


                        • count once within a time interval
                          Damian Spyra

                          Hi flipside,


                          i've done this in script:




                               Event ID,


                               Init Date,

                               Date - Init Date as DaysPast,


                          FROM SourceTable GROUP BY Event ID, ORDER BY Date;


                          and then,





                               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!