Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate wait times in minutes and group them

Hi all,

I am looking to calculate wait times wiz

present time - arrival_time (format is in example 17/12/2012 15:30:00)

I calculated by using an expression

Interval(now(date(reloadtime())) - arrival_time, 'mm')

The above expression works perfectly and i get the minutes. Stored it in a variable vMin

I now need to group it in minutes.

Example 0-15, 16-30...etc...

In the load script i wrote the statements

If(&amp;(vmin) >=00 and &amp;(vmin) <= 15, '0-15',

If(&amp;(vmin) >=16 and &amp;(vmin) <=30, '16-30)) as min_grp

When i create a chart and pull the dimension min_grp,

The chart only shows 0-15 min as a group. Doesnt show 15-30.

Not sure where i am going wrong.

Rdgards,

Kv

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hi,

You can do a precedent load that gets the calculated fields and creates the range texts:

LOAD *,

     If(Difference < Interval(Interval#(15, 'mm')), '0 - 15',

       If(Difference < Interval(Interval#(30, 'mm')), '16 - 30',

         // more conditions here

     )) AS Min_Groups // uses the field calculated below

;

LOAD // Fields here

     Interval(Now() - arrival_time, 'mm') AS Difference

FROM // Source here

Your If() is always returning the "then" part because, unless specified otherwise, Interval() gets the first parameter as days, even when this are supposed to be minutes. Interval(1, 'mm') means "tell me the number of minutes in 1 day" and will return 1440. Interval(15, 'mm') will return 1440 times 15... therefore the use of Interval#() in my example code above.

Hope that helps.

Miguel

View solution in original post

6 Replies
Gysbert_Wassenaar

Interval returns the time difference in units of day. So <=15 means smaller than 15 days. So everything will like fall into this range.

Interval(now(date(reloadtime())) - arrival_time, 'mm') doesn't make sense to me. The now() function takes 0,1 or 2 as argument, not a date.


talk is cheap, supply exceeds demand
Not applicable
Author

What might be the right function?.

Thanks.

Miguel_Angel_Baeyens

Hi,

You can do a precedent load that gets the calculated fields and creates the range texts:

LOAD *,

     If(Difference < Interval(Interval#(15, 'mm')), '0 - 15',

       If(Difference < Interval(Interval#(30, 'mm')), '16 - 30',

         // more conditions here

     )) AS Min_Groups // uses the field calculated below

;

LOAD // Fields here

     Interval(Now() - arrival_time, 'mm') AS Difference

FROM // Source here

Your If() is always returning the "then" part because, unless specified otherwise, Interval() gets the first parameter as days, even when this are supposed to be minutes. Interval(1, 'mm') means "tell me the number of minutes in 1 day" and will return 1440. Interval(15, 'mm') will return 1440 times 15... therefore the use of Interval#() in my example code above.

Hope that helps.

Miguel

Not applicable
Author

Hi Miguel,

I did try with the above script, it gives the list of minutes in xaxis. What I am trying to achieve is to group the minutes on x axis. The time difference in minutes I want to calculate is only for current day.

Miguel_Angel_Baeyens

Hi,

I may be missing something, but you are actually grouping the values in the If() of the precedent LOAD. Give that a name, as in the example above "Min_Groups" and use this instead as dimension in the chart.

Hope that helps.

Miguel

Not applicable
Author

Thanks Miguel.  As I said, the above code worked. The only difference was that in the interval function wiz

Interval(Now() - arrival_time, 'mm'), I changed it to Interval(Now(Date(ReloadTime()))  - arrival_time, 'mm')

Thank you very much.