topic Re: Exclude fields with a specific value - KPI in App Development

Exclude fields with a specific value - KPI

richardm90 — Tue, 09 Jan 2018 17:02:06 GMT

Hi All,

I'm developing a few KPI's in Qlik Sense. These KPI's are working out the percentage of appointments that happened within a certain amount of days of booking. The total percentage is the Booking_ID.

However I need to exclude Booking ID's that include ABC at the end. I currently have this:

Sum(Aggr(If(NetWorkDays("[Booking_Date]", "[Appointment_Date]") <= 11, 1, 0), [Booking_ID]))/count([Booking_ID]

This works great but doesn't exclude booking ID's that end with ABC.

Any help with this would be great.

Re: Exclude fields with a specific value - KPI

swuehl — Tue, 09 Jan 2018 17:14:23 GMT

Maybe

Sum(

{<[Booking ID] -= {"*ABC"} >}

Aggr(If(NetWorkDays("[Booking_Date]", "[Appointment_Date]") <= 11, 1, 0), [Booking_ID]))/count([Booking_ID]

edit: or if you also need to exclude from the total:

Sum(

{<[Booking ID] -= {"*ABC"} >}

Aggr(If(NetWorkDays("[Booking_Date]", "[Appointment_Date]") <= 11, 1, 0), [Booking_ID]))

/ count({<[Booking ID] -= {"*ABC"} >} [Booking_ID]

Re: Exclude fields with a specific value - KPI

richardm90 — Sun, 14 Jan 2018 14:42:18 GMT

That's great Stefan thank you.

My other issue is that the data is coming from multiple sources now, so I need to work out the percentage by adding results together. SO far I have tried:

Sum(

{<[Booking ID] -= {"*ABC"} >}

Aggr(If(NetWorkDays("[Booking_Date]", "[Appointment_Date]") <= 11, 1, 0), [Booking_ID]))

Sum(

{<[Booking ID] -= {"*ABC"} >}

Aggr(If(NetWorkDays("[Booking_Date2]", "[Appointment_Date]") <= 11, 1, 0), [Booking_ID]))/count([Booking_ID]

Sum(

{<[Booking ID] -= {"*ABC"} >}

Aggr(If(NetWorkDays("[Booking_Date3]", "[Appointment_Date]") <= 11, 1, 0), [Booking_ID]))/count([Booking_ID]

/count([Booking_ID]

But this does not work. When I add the 3 results together like this it does not give me the correct result to then divide by the total Booking ID's

Re: Exclude fields with a specific value - KPI

swuehl — Sun, 14 Jan 2018 16:50:36 GMT

You are missing the division in your first part.

But not sure if this is what you need, a more detailed explanation of your model would be more than helpful.

Re: Exclude fields with a specific value - KPI

richardm90 — Sun, 14 Jan 2018 19:02:19 GMT

Ahh that was a typo.

Ok, so I have 621 records to analyse across multiple data sources.

I calculated that there are 61 relevant records out of this by calculating the amount of records (linked by the BOOKING_ID column) that have a booking date and an appointment date with this calculation:

count(NetWorkDays("Booking_Date", "Start Time"))+

count(NetWorkDays("Booking_Date2", "Start Time"))+

count(NetWorkDays("Booking_Date3", "Start Time"))

This gave me the 61.

I then calculated the amount of appointments (start time) that occurred within 10 working days of the booking date with this calculation:

Sum(

{<[Booking ID] -= {"*ABC"} >}

Aggr(If(NetWorkDays("[Booking_Date]", "[Start Time]") <= 11, 1, 0),[Booking ID]))

Sum(

{<[Booking ID] -= {"*ABC"} >}

Aggr(If(NetWorkDays("Booking_Date2") <= 11, 1, 0),[Booking ID]))

Sum(

{<[Booking ID] -= {"*ABC"} >}

Aggr(If(NetWorkDays("Booking_Date3") <= 11, 1, 0),[Booking ID]))

This gave me 42.

I now need to calculate this as a percentage (42 out of 61)

Re: Exclude fields with a specific value - KPI

swuehl — Sun, 14 Jan 2018 19:55:48 GMT

Ok, what's wrong with dividing the second part by the first part then?

I don't think that you need the Networkdays() function in your Count() functions in the first part, but since I still don't have an idea how your model looks like, this is just a guess.

Re: Exclude fields with a specific value - KPI

richardm90 — Mon, 15 Jan 2018 00:16:28 GMT

The {<[Booking ID] -= {"*ABC"} >} type function does not work correctly.

I am also using this in another KPI where I need to exclude appointments that were cancelled.

My formula is:

Sum(

{<Cancelled -= "Cancelled" >}

Aggr(If(NetWorkDays("[Booking_Date]", "Start Time") <= 11, 1, 0),[Booking_ID]))

This should not include entries where the cancelled column includes the text "Cancelled".

Any help would be VERY much appreciate don this one guys.

Re: Exclude fields with a specific value - KPI

swuehl — Mon, 15 Jan 2018 10:16:20 GMT

Still, no idea how your model and data looks like and what may cause your expression to "not work correctly" (which is not a very good issue description. Please add the model context, your expectation and the observed results).

Maybe this blog post by Henric is of help

Excluding values in Set Analysis