Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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')

View solution in original post

2 Replies
swuehl
MVP
MVP

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')

Not applicable
Author

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!