Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

richardm90
New Contributor II

Exclude fields with a specific value - KPI

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.

7 Replies
MVP
MVP

Re: Exclude fields with a specific value - KPI

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]

richardm90
New Contributor II

Re: Exclude fields with a specific value - KPI

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

MVP
MVP

Re: Exclude fields with a specific value - KPI

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.

richardm90
New Contributor II

Re: Exclude fields with a specific value - KPI

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)

MVP
MVP

Re: Exclude fields with a specific value - KPI

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.

richardm90
New Contributor II

Re: Exclude fields with a specific value - KPI

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.

MVP
MVP

Re: Exclude fields with a specific value - KPI

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

Community Browser