6 Replies Latest reply: Feb 12, 2018 5:56 PM by Ric Morgan

Interval Function

Ric Morgan Jan 14, 2018 9:24 PM

Hi All,

I'm creating some KPI's in Qlik Sense to show the total number of appointments that lasted a certain amount of time...

I'm currently using this formula to calculate the interval of every appointment.

=interval(date([End Time], 'hh:mm') - date([Start Time], 'hh:mm'), 'hh:mm')

This gives me a combination of 30mins, 60mins, 90mins and 120 mins appointment lengths.

How do I now create a KPI to display only the 30mins total? i.e. out of 80 appointments 5 lasted 30 mins.

Re: Interval Function

Jonathan Dienst Jan 14, 2018 11:43 PM (in response to Ric Morgan )

Do you want to count appointments? Then something like:
=Count(If([End Time] - [Start Time] > 30 / (24 * 60), AppointmentID))

(Interval() and Date() are merely formatting functions, they have no effect on the underlying value. 30 / (24 * 60) is the value of 30 minutes. You could also use Time#('00:30:00'))
Like Show 0 Likes (0)

Actions
- Re: Interval Function
  
  Ric Morgan Jan 17, 2018 8:51 AM (in response to Jonathan Dienst )
  
  Hi Jonathan,
  
  Thanks for your help on this. I have implemented this formula but am getting incorrect results.
  
  There are 175 appointments and only 9 are 30 mins long. However the formula above is giving the result of 171.
  
  Any idea where I could be going wrong here.
  
  Like Show 0 Likes (0)
  
  Actions
- Re: Interval Function
  
  Ric Morgan Jan 17, 2018 9:47 AM (in response to Jonathan Dienst )
  
  I also tried the Time#('00:30:00')) function and changed my timeformat to SET TimeFormat='hh:mm:ss' but it gave the same result. How come =Count(If([End Time] - [Start Time] = 30 / (24 * 60), AppointmentID)) does not work in this case? I just get 0.
  
  Like Show 0 Likes (0)
  
  Actions
Re: Interval Function

Ric Morgan Jan 19, 2018 5:53 AM (in response to Ric Morgan )

Any further help on this would be much appreciated.
Like Show 0 Likes (0)

Actions
- Re: Interval Function
  
  Jonathan Dienst Jan 19, 2018 6:07 AM (in response to Ric Morgan )
  
  Do you have valid date values? The code assumes the Start Time and End Time are time or time-stamp numeric values, and not strings.
  
  QlikView Date fields
  Why don’t my dates work?
  
  And that both time are correctly associated with the Appointment IDs.
  
  I suggest that you share a small sample qvf that illustrates the problem and someone may be able to pinpoint the problem.
  
  Like Show 1 Like (1)
  
  Actions
  - Re: Interval Function
    
    Ric Morgan Feb 12, 2018 5:56 PM (in response to Jonathan Dienst )
    
    Thank you for the reply Jonathan,
    
    Yes the dates are time stamp numeric values. They are definitely associated with the appointment ID's. I am using the same date data in other KPI's which work fine.
    
    I'll share a sample qvf shortly.
    
    Thanks again for the help.
    
    Like Show 0 Likes (0)
    
    Actions

Go to original post