Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

richardm90
New Contributor II

Interval Function

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.

6 Replies
MVP
MVP

Re: Interval Function

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

richardm90
New Contributor II

Re: Interval Function

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.

richardm90
New Contributor II

Re: Interval Function

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.

richardm90
New Contributor II

Re: Interval Function

Any further help on this would be much appreciated.

MVP
MVP

Re: Interval Function

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.

richardm90
New Contributor II

Re: Interval Function

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.