7 Replies Latest reply: Jan 15, 2018 5:16 AM by Stefan Wühl RSS

    Exclude fields with a specific value - KPI

    Ric Morgan

      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
          Stefan Wühl

          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
              Ric Morgan

              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
                  Stefan Wühl

                  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
                      Ric Morgan

                      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)