2 Replies Latest reply: Feb 14, 2013 11:09 AM by David Najar RSS

    Help with strange expresion using Aggr

      Good afternoon,

       

      I'm facing some problems while using Aggr() and Below() functions.. Probably I need a coffee break soon, and that's why I'm not able to find a clear solution for this

       

      Let's say I have this data:

       

      ID                             CTime    

      1                         14/02/2013 15:21:00

      2                         14/02/2013 15:21:17

      3                         14/02/2013 15:21:30

      4                         14/02/2013 15:22:40

      5                         14/02/2013 15:22:59

       

       

      I have used Below Function in order to have the time to next event:

           Time(Interval(Below(Time)-Time))

      and it works great

       

      ID                              CTime                               Time(Interval(Below(CTime)-CTime))              

      1                         14/02/2013 15:21:00                                        17

      2                         14/02/2013 15:21:17                                        13

      3                         14/02/2013 15:21:30                                        70

      4                         14/02/2013 15:22:40                                        19    

      5                         14/02/2013 15:22:59                                         -      (Null since there is no below record)   

       

       

       

      Also, using the Aggr Function, I could have in a text box, Max time between events, min time, average, etc.

       

      What I really want to know is wether is possible to obtain in a text box (using aggr function I supose)  "The sum of Intervals where interval is greather than x"

      In the example avobe, and imagin  x = 18, the textbox should display 89  (19+70) 

       

      After lot of tries, I'm starting to be a bit confused... even for explain the situation...

       

      Thanks in advance!

        • Re: Help with strange expresion using Aggr
          Stefan Wühl

          At least two things to notice:

           

          - Aggr() dimension values are sorted by load order, so you need to take care that load order (I assume you are using ID as dimension in your aggr() function) is in the order you need.

           

          - You are calculating a difference of timestamps, so you get something back as fraction of days, a number that you are displaying only as seconds. So when comparing the result of your difference calculatin, you need to compare to an interval, too:

           

          interval#( '18', 'ss')

           

          so maybe in total try something like:

           

          =interval( sum( aggr( if(Below(CTime)-CTime >= interval#('18','ss'),Below(CTime)-CTime),ID) ) ,'ss')

            • Re: Help with strange expresion using Aggr

              swuehl escribió:

               

              At least two things to notice:

               

              - Aggr() dimension values are sorted by load order, so you need to take care that load order (I assume you are using ID as dimension in your aggr() function) is in the order you need.

               

              - You are calculating a difference of timestamps, so you get something back as fraction of days, a number that you are displaying only as seconds. So when comparing the result of your difference calculatin, you need to compare to an interval, too:

               

              interval#( '18', 'ss')

               

              so maybe in total try something like:

               

              =interval( sum( aggr( if(Below(CTime)-CTime >= interval#('18','ss'),Below(CTime)-CTime),ID) ) ,'ss')

               

              That's Great! it worked Perfectly!

              Just let me tell you my mistakes (probably someone else is having similar ones):

              As you said, i was taking care of converting the value in days back to seconds.

              The main problem is that i was focused on do the aggregate, and then sum if condition... don't know why.

              ie.

               

              sum(if(aggr(Below(CTime)-CTime,ID) >= interval#('18','ss'), aggr(Below(CTime)-CTime,ID) ,0)

               

              Please don't ask me why i was doing this way... finally it was easier than I expected. Anyway, without your help would be impossible! thx a lot !

               

               

              PS also I tried comparing directly with 18 (   Below(CTime)-CTime,ID) >18)  without taking care of the interval# function... that's great

               

               

              Thanks again!