Interval Function

[richardm90]

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.

[jonathandienst]

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]

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]

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]

Any further help on this would be much appreciated.

[jonathandienst]

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]

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.