Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Employee
Employee

How to create a median of percentage of completed help desk tickets

All,

Attached is a sample Qlikview document showing a group of helpdesk tickets, the ID's associated with them, the month/year submitted, and the time to resolve.  The issue is in trying to create a median of the SLOWEST 30% of just the completed tickets. The actual "median" function will not work in this scenario due to a need for aggregation.

Example:

 

Month-YrIDTimeStatus
Dec-2014125Completed
Dec-2014224Completed
Dec-2014320Completed
Dec-2014417Completed
Dec-2014510Not Resolved
Dec-201468Completed
Dec-201485Not Resolved
Dec-201475Completed
Dec-201493Completed
Dec-2014102

Completed 

and so on.....

For a month where there are 70 tickets, 60 of them were resolved, 30% is 18 tickets. The requirement is to find the median of those 18 tickets. 

I have been unable to create the final calculation for the median due to the restriction of only using a dynamic 30% set for each month.

In the real application, set analysis on 3 fields is needed for all calculations to force the "completed' and 2 other filters at all times. Even without those, this is a difficult expression.  Adding a < or > to compare a value for <30% or >30% fails in every scenario tried so far.

I have had some good suggestions for finding the fastest or overall median that have worked well, but the slowest one has me stumped.

Any ideas?

Very much appreciate any suggestions! !

1 Solution

Accepted Solutions
Employee
Employee

Re: How to create a median of percentage of completed help desk tickets

The following ought to work for the slowest tickets (and for the fastest you just replace <=0.3 with >=0.7):

=Median({...}

     Aggr(

          If(Rank(Only({...} TimeSpent),4,1)/Count({...} total <[Month-Yr]> ID)<=0.3, TimeSpent ),

          [Month-Yr],ID

          )

     )

where you need to replace all three Set Analysis expressions with {$<Status={Completed}>}

HIC

7 Replies

Re: How to create a median of percentage of completed help desk tickets

I kind of wonder about the metric, but here's a try:

median({<Status={Completed}>}

  if(TimeSpent<=aggr(NODISTINCT Fractile({<Status={Completed}>}TimeSpent, .3), [Month-Yr])

,TimeSpent))

It doesn't sample the bottom 30% of tickets, but rather those tickets that are <= 30th percentile for TimeSpent.

-Rob

lastgoodusernam
New Contributor III

Re: How to create a median of percentage of completed help desk tickets

Open a new script and enter:

ExampleData:

Load

    *

Inline

[

TicketID, Period,TimeToComplete

1,1,12

2,1,4

3,1,8

4,1,15

5,1,5

6,1,2

7,2,9

8,2,14

9,2,1

10,2,16

];

Textbox for median:

='Total Median: ' & Median(TimeToComplete)

Textbox for bottom 30% median:

='Slowest 30% Median: ' & median(if(aggr(rank(max(TimeToComplete)),TicketID)<= count (total TimeToComplete) / 3,TimeToComplete))

This solution ranks the values using aggr, but keeps only those where the rank is in the top third (or, more specifically, those where the rank is less than the total N / 3).

So in this example, bottom third contains 14, 15, and 16. The median of these three values is 15.

20151111_1.JPG

You can combine with set analysis to include only the completed tickets.

I'm sorry that I cannot see your file, but I only have access to the personal edition right now. I apologize if I'm missing anything.

Employee
Employee

Re: How to create a median of percentage of completed help desk tickets

Rob,

Thank you so much for replying.  It's very close, but I think perhaps I wasn't clear that I needed the slowest times, which would be the largest time spent. Here's an version of my chart with the medians I need to see:

     

Month-Yr# TicketsList of Times# of Tickets making 30%Median of 30% Slowest (greatest times)
Dec-201482,3,5,8,17,20,24,252.4 (round to 2)24.5
Feb-2015151,1,1,2,2,2,3,3,3,4,4,6,9,15,4059
Jan-2015814,15,18,18,20,25,40,802.4 (round to 2)60

I tried using the reverse of your expression, replacing .3 with .7 and got very close, but it's off by 1 position. For Dec, I got 24, for Feb, I got 7.5 (avg of 6 & 9)

How could I tweak this to get the correct position/number?

Thanks!

Re: How to create a median of percentage of completed help desk tickets

I like LastGoodUserNam answer. I think that gives you what you want.

-Rob

Employee
Employee

Re: How to create a median of percentage of completed help desk tickets

Thank you for responding, LastGoodUserNam.  I'm sorry, but this doesn't work for data where the medians must be aggregated by date.  Here are the results using the real data with your expression:

      

Month-Yr# Tickets30% of TicketsList of timesMedian using your expressionMedian needed
Dec-2014822,3,5,8,17,20,24,252424.5
Feb-20151551,1,1,2,2,2,3,3,3,4,4,6,9,15,40409
Jan-20158214,15,18,18,20,25,40,8022.560

The median must take all of the times, rank them for the specific month, calculate how many tickets make 30% of the completed tickets and then find the median of those tickets for that month.

Still looking for a solution.

Employee
Employee

Re: How to create a median of percentage of completed help desk tickets

The following ought to work for the slowest tickets (and for the fastest you just replace <=0.3 with >=0.7):

=Median({...}

     Aggr(

          If(Rank(Only({...} TimeSpent),4,1)/Count({...} total <[Month-Yr]> ID)<=0.3, TimeSpent ),

          [Month-Yr],ID

          )

     )

where you need to replace all three Set Analysis expressions with {$<Status={Completed}>}

HIC

Employee
Employee

Re: How to create a median of percentage of completed help desk tickets

Henric,

Thank you! This creates the slowest median of the completed tickets as was needed.

Mari

Community Browser