6 Replies Latest reply on Mar 1, 2018 1:33 AM by Ivana Foukalova

# Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual x-axis, scaffold,...?)

Good morning!

The below visualization is a significant challenge for me to replicate in Tableau as after having explored the forum and Tableau help and trying several approaches using various LODs, scaffold, dual x-axis..., I was not able to get much closer to the wanted result.

GOAL is to calculate “tickets closing speed” with x axis showing number of days and y axis showing 100% - ratio of tickets closed within n days of opening vs. all tickets that could have been closed within n days. Sample  data and outcome as per picture below (several first records only) and attached Excel (full sample data set and related calc.).

 Original Dataset Calculations Ticket ID Ticket Opened Date Ticket Closed Date MaxReportDate Ticket Closed (1 = yes, 0 = no) Days Ticket Opened to MaxReportDate Ticket Age 1 2/4/2017 2/7/2017 2/8/2017 1 4 3 2 1/30/2017 2/7/2017 2/8/2017 1 9 8 3 1/29/2017 2/8/2017 0 10 10 4 1/28/2017 2/8/2017 0 11 11 5 1/27/2017 1/30/2017 2/8/2017 1 12 3 6 1/22/2017 1/23/2017 2/8/2017 1 17 1 7 1/17/2017 1/17/2017 2/8/2017 1 22 0

 Aggregation Days to close All relevant tickets Tickets Closed Closing Speed 0 17 1 94% 1 17 3 82% 2 17 3 82% 3 17 5 71% 4 17 6 65%

The data set is simplified as in reality:

- there are attributes related to each ticket that I would like to use as filters

- may be working with date&time instead of days

- I would also like to show different lines for different periods of time, i.e. to compare each tickets closing speed for tickets opened in last 14 days vs. last 30 days (or last quarter....)

Any advice/hint on the best approach to this problem will be truly very much appreciated.

Thank you and best regards,

Ivana

• ###### 1. Re: Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual x-axis, scaffold,...?)

This is a tricky question. Unfortunately I don't have an exact solution to offer, but I would suggest exploring either calculating the denominator and numerator using correlated subqueries in a SQL pre-processing step, or making a call to R to do those calculations for you.

1 of 1 people found this helpful
• ###### 2. Re: Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual x-axis, scaffold,...?)

As Stephan says, this is quite a bit tricky....

I am going to show you it's not Impossible, but you can understand how complex this is...

Here is the solution using Table calc.  Not sure there is much easier way..

First of all, to have complete table from 0-11 of "Days to Close"

I created another sheet like below.

This is consist of Dyas to Close (0-11) x Ticket ID (1-17) = 204 line items. (You can see "Master")

Then duplicate data source and blend with above Master as below.

[Days Ticket Opened to MaxReportDate 2]

min(11,[Days Ticket Opened to MaxReportDate])

[All Items]

window_sum(count([Ticket ID]))

[Calculation1]

WINDOW_Max(if max([Sample (TestSampleData)].[Days Ticket Opened to MaxReportDate 2])

>=min([Days to close]) then 1 else 0 end)

[Calculation11]

if window_min(min([Sample (TestSampleData) (copy)].[Ticket Age]))

<= min([Days to close])

and

window_min(min([Sample (TestSampleData)].[Days Ticket Opened to MaxReportDate 2]))

>= min([Days to close])

then window_SUM(SUM([Sample (TestSampleData) (copy)].[Ticket Closed])) else 0  end

[Ticket Closed]

window_sum([Calculation11])

[All Relevant Ticket]

ifnull([All Items] -window_sum([Calculation1],first(),-1),[All Items])

[Calculation3]

([Calculation12])/([Calculation2])([Ticket Closed])/([All Relevant Ticket])

[Closing Speed]

1-([Calculation3])

[index()]

index()

Then assign the table calculation for  [Closing Speed]

This past is most difficult.

Again it's quite tricky, it may be better that you select excel to calculate/create table and use Tableau to draw charts.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 3. Re: Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual x-axis, scaffold,...?)

Hi Shin,

Thank you so much for the time you took to provide such detailed and instrumental reply, I wouldn't be able to come up with the calculations/solutions myself. Due to the nature of data set (ten thousands of tickets, frequent data refresh), I will explore further the data pre-processing part but this shall be now - with yours and Stephen's advice - much easier.

Thanks again very much. Have a great day!

Ivana

• ###### 4. Re: Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual x-axis, scaffold,...?)

Stephen,

many thanks for directing me to areas to explore. While looking for the SQL pre-processing, I am finding posts with tasks similar to mine - this helps a lot.

Cheers!

• ###### 5. Re: Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual x-axis, scaffold,...?)

Ivana,

Sounds good.

If you don't mind, could you attach the link to the post you are referring.

That's helps us a  lot o close this thread..

Thanks,

Shin

• ###### 6. Re: Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual x-axis, scaffold,...?)

Hi Shin,

only recently I have been able to get back to this topic, with the following update and solution (Tableau version used: 10.1) that I believe is less complex and allows for filtering by ticket's attribute as well:

• technique used: cross-database joins
• two data tables joined with a "artificial" key of 1 to create a product join:

• then calculations are used to identify for each day evaluated all relevant tickets (Cohort 1 Relevant Case, i.e. those that could have been closed within the specified number of days) and closed tickets (Cohort 1 Closed Case, i.e. those that were closed at or before the specified number of days)
• the closing speed by day is then calculated using the formula 1- sum(closed ticket)/sum(relevant ticket)

Side note: I am using Cohort 1 in the attached file as I will be comparing tickets closing speed for different periods of time but it is not relevant at all for the concept here. Also, I am using case and ticket interchangeably here.

Resources that led to the above and attached are:

• Tableau Community post (some of the specifics mentioned in the post may not be relevant any more, e.g. the restrictions on Extracts doesn't seem to be valid any more): CROSS JOIN with Tableau's join dialog
• Recording from Tableau Conference 2016 session on Cross-Database Joins (+there are other videos on this topic that I haven't had yet chance to watch)

Hope this above might help other folks approaching similar problems.Thanks for your support.

Have a nice day and weekend!

Ivana

1 of 1 people found this helpful