Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am releatively new to QlikView and have 2 questions:
(1) I was wondering how to write an expression that generates an output list conditional on certain fields being either available or unavailable in the data.
For example, I need to generate an output list for today's date (01/07/2013) for any requests (sample below) that do not have a visit date within 3 day of the request date AND do have both a processing date and an appointment date BUT do not have a completed date.
Today - 01/07/2013
Request #1 - AAA
Request date - 01/01/2013
Processing date - 01/01/2013
Appointment date - no data
Visit date - no data
Complete date - no data
Request #2 - BBB
Request date - 01/01/2013
Processing date - 01/01/2013
Appointment date - 01/02/2013
Visit date - no data
Complete date - 01/07/2013
Request #3 - CCC
Request date - 01/01/2013
Processing date - 01/01/2013
Appointment date - 01/02/2013
Visit date - no data
Complete date - no data
In this example, the only output in the list should be Request #3 - CCC.
(2) In additon, I need to generate a "missing data" rate for any requests completed on today's date but that are missing dates for the appointment date and visit date. In this case, the missing data rate should be 100% because the only request completed on 01/07/2013 is missing a visit date. The expression i am currently using is:
Where Dimension is Complete Date .... =(count(if(Appointment Date]=null() or Visit Date = null(),[Request #],null()))/count([Request #]))
Please advise.
Thanks for the help!!
I finally got some time to play around with your second request. See if this is what you had in mind for the missing data percentage. Hope it helps!
Following your logic for #1, wouldn't both Request 1 and Request 3 be shown? I don't have time today to look at #2, but take a look at the attached sheet and see if it gets you any closer.
Hi Rebecca,
Thanks for your swift response!
I've been trying to work with the sheet you attached and am still having trouble. Also, I may have not been as clear as I would have liked in the instructions...Request 1 should not be shown because it did not have an appointment date.
My primary confusions surrounds what this part of the fuction is supposed to do:
(VisitDate>(RequestDate+3) or VisitDate<(RequestDate+3) or IsNull(RequestDate))
I tried to iterate on your expression, and this got me a little closer to the answer, but i'm still scrambling a bit! 🙂 On change I made is to make the look-up date for the data the Request Date (01/01/2013) rather than Today (01/07/2013):
=if([Request Date]+3)
and
(IsNull[Visit Date])
and
([Processing Date]<>0 or [Processing Date]<>'')
and
([Appointment Date]<>0 or [Appointment Date]<>'')
and
(IsNull [Complete Date]=-1 or [Complete Date]=''),1)
Thanks again, Rob
Ah, I see. Take a look at the attached document. Item #3 is the only one shown.
I finally got some time to play around with your second request. See if this is what you had in mind for the missing data percentage. Hope it helps!
Thanks for the help, Rebecca!