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