
Re: How to create a median of percentage of completed help desk tickets
Rob Wunderlich Nov 11, 2015 10:13 PM (in response to Mari Borries )I kind of wonder about the metric, but here's a try:
median({<Status={Completed}>}
if(TimeSpent<=aggr(NODISTINCT Fractile({<Status={Completed}>}TimeSpent, .3), [MonthYr])
,TimeSpent))
It doesn't sample the bottom 30% of tickets, but rather those tickets that are <= 30th percentile for TimeSpent.
Rob

Re: How to create a median of percentage of completed help desk tickets
Mari Borries Nov 11, 2015 10:37 PM (in response to Rob Wunderlich )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:
MonthYr # Tickets List of Times # of Tickets making 30% Median of 30% Slowest (greatest times) Dec2014 8 2,3,5,8,17,20,24,25 2.4 (round to 2) 24.5 Feb2015 15 1,1,1,2,2,2,3,3,3,4,4,6,9,15,40 5 9 Jan2015 8 14,15,18,18,20,25,40,80 2.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
Jeff Menzie Nov 11, 2015 10:25 PM (in response to Mari Borries )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.
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.

Re: How to create a median of percentage of completed help desk tickets
Mari Borries Nov 12, 2015 9:23 AM (in response to Jeff Menzie )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:
MonthYr # Tickets 30% of Tickets List of times Median using your expression Median needed Dec2014 8 2 2,3,5,8,17,20,24,25 24 24.5 Feb2015 15 5 1,1,1,2,2,2,3,3,3,4,4,6,9,15,40 40 9 Jan2015 8 2 14,15,18,18,20,25,40,80 22.5 60 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.


Re: How to create a median of percentage of completed help desk tickets
Henric Cronström Nov 16, 2015 9:13 AM (in response to Mari Borries )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 <[MonthYr]> ID)<=0.3, TimeSpent ),
[MonthYr],ID
)
)
where you need to replace all three Set Analysis expressions with {$<Status={Completed}>}
HIC

Re: How to create a median of percentage of completed help desk tickets
Mari Borries Nov 16, 2015 10:50 AM (in response to Henric Cronström )Henric,
Thank you! This creates the slowest median of the completed tickets as was needed.
Mari
