Tracking turnaround times Quartiles month on month

Khan

Hi,

We need to report by month the turnaround time (resolved time - created time) for each Quartile.

The top 25% quartile is time for top 25% of the requests when arranged in ascending order of their turnaround time. Similarly top 50% quartile is the median i.e. 50th percentage mark when requests are arranged in ascending order of the time parameter being measured. Top 75% quartile would be the 75th percentage mark.

The chart type would be a bar chart. We should be able to filter on Quartile since it's possible that top 25 quartile would be too low compared to top 75 quartile and wouldn't show in the bar chart. So a user can filter on top 25% quartile and it would show bar chart for each month for that quartile. 

Can you please guide me how to accomplish this?

Thanks in advance!

GrantSmith

Hi @Khan

You'll want to calculate the percentile rank within a Magic ETL before you pull it into your card. Use the Rank & Window tile and Rank your times. Add a Group By tile to get a Count of the total rows in your dataset. Join it back to the Rank & Window tile so that the total row count is now a column next to every rank. Then do the math in the ETL to determine the percentile (rank / total records). This will then give you a percentile rank (0-1 You can multiple the number in the ETL by 100 for better readability) the user can filter based upon.

Khan

@GrantSmith Thank you. In Group By, Ticket ID is aggregated by Count, Select What columns identify the grouping shouldn't include Ticket ID else it will be 1 per row.

If I do not include Ticket ID in Select What columns identify the grouping, while joining I have to join using matching values i.e., Ticket ID in Rank and Window Tile and Ticket ID in Group By.

Please suggest.

jaeW_at_Onyx

@Khan and @GrantSmith there are features for Median() and NTile() in Analyzer, just ask your CSM to "turn on Median in Analyzer"