topic Re: date compare calculation in QlikView

date compare calculation

mdurso26 — Thu, 31 Mar 2016 20:01:58 GMT

Hi all! I'm looking for help in comparing the difference between two date fields to a third, static number. I have two fields (APPT_DTTM and APPT_CANC_DTTM) that I need to find the difference between, then take that difference and compare it to a static number (24 hours). What would be the best way to go about this? The APPT_DTTM and APPT_CANC_DTTM fields are in the same format (example: 7/6/2009 9:28:44 AM shows as 40000.416666667 without a Timestamp conversion).

What I'm trying to do is show the percentage of appointments that have been cancelled within 24 hours (APPT_CANC_DTTM) of the scheduled appointment time (APPT_DTTM). To do this I think I would need to find the difference between the two times, compare that difference to 24 hours, and return a "1" if the difference is within 24 hours of the scheduled appointment time and a "0" if it is outside of 24 hours of the scheduled appointment time. I can then sum all the "1"'s and divide that by the total number of appointments to get my percentage; this part I can do, it's just getting those "1"'s I can't figure out.

Any help you can provide would be HUGELY appreciated!!

Thanks,

Matt

Re: date compare calculation

swuehl — Thu, 31 Mar 2016 21:21:52 GMT

Your sample number for a timestamp returns 0706//2009 10:00:00 on my system, but basically, I think you should be fine with something like

=Sum( If( Fabs(APPT_DTTM - APPT_CANC_DTTM) <= 1,1,0))

Then divide by the number of appointments.

Re: date compare calculation

maxgro — Thu, 31 Mar 2016 21:30:20 GMT

for time difference maybe you can use the "-"

1 is 24 hours = 1 day

if time difference >=1 then 1 else 0

if((APPT_DTTM - APPT_CANC_DTTM )>=1, 1, 0)

Re: date compare calculation

MarcoWedel — Thu, 31 Mar 2016 23:52:58 GMT

maybe

-(APPT_DTTM-APPT_CANC_DTTM<=1)

Sign(Floor(APPT_DTTM-APPT_CANC_DTTM))

(provided always APPT_DTTM>=APPT_CANC_DTTM)

regards

Marco

Re: date compare calculation

mdurso26 — Fri, 01 Apr 2016 17:33:15 GMT

Thanks for your reply! I need to do the comparison part to other times in addition to 24 hours. I'm looking to compare the difference to 24 hours, 16 hours, 12 hours, 8 hours, 4 hours, 2 hours, and 1 hour within the scheduled appointment time. Would I do that like below?

24 hours

if((APPT_DTTM - APPT_CANC_DTTM )>=1, 1, 0)

16 hours

if((APPT_DTTM - APPT_CANC_DTTM )>=0.6667, 1, 0)

12 hours

if((APPT_DTTM - APPT_CANC_DTTM )>=0.5, 1, 0)

8 hours

if((APPT_DTTM - APPT_CANC_DTTM )>=0.3334, 1, 0)

4 hours

if((APPT_DTTM - APPT_CANC_DTTM )>=0.1667, 1, 0)

2 hours

if((APPT_DTTM - APPT_CANC_DTTM )>=0.0833, 1, 0)

1 hour

if((APPT_DTTM - APPT_CANC_DTTM )>=0.0416, 1, 0)

Re: date compare calculation

swuehl — Fri, 01 Apr 2016 17:40:10 GMT

Exactely, the fractional part of a timestamp or time interval is stating part of a day, i.e. part of 24 hours.

12 hours = 12/24 = 1/2

2 hours = 2/24 = 1/12

etc.

edit: And remember to use aggregation functions if you need to aggregate multiple records, e.g. in a chart expression or text box.

Like the suggested Sum(If( ....)) above.