
1. Re: Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual xaxis, scaffold,...?)
Stephen Rizzo Feb 20, 2017 1:48 PM (in response to Ivana Foukalova)1 of 1 people found this helpfulThis 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 preprocessing step, or making a call to R to do those calculations for you.

2. Re: Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual xaxis, scaffold,...?)
Shinichiro Murakami Feb 20, 2017 7:54 PM (in response to Ivana Foukalova)1 of 1 people found this helpfulAs 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 011 of "Days to Close"
I created another sheet like below.
This is consist of Dyas to Close (011) x Ticket ID (117) = 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

3. Re: Calculation of Tickets Closing Speed in % (conditional running_sum/deduction, dual xaxis, scaffold,...?)
Ivana Foukalova Feb 21, 2017 12:07 AM (in response to Shinichiro Murakami)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 preprocessing 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 xaxis, scaffold,...?)
Ivana Foukalova Feb 21, 2017 12:10 AM (in response to Stephen Rizzo)Stephen,
many thanks for directing me to areas to explore. While looking for the SQL preprocessing, 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 xaxis, scaffold,...?)
Shinichiro Murakami Feb 21, 2017 5:21 AM (in response to Ivana Foukalova)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 xaxis, scaffold,...?)
Ivana Foukalova Mar 1, 2018 1:33 AM (in response to Shinichiro Murakami)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: crossdatabase 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 CrossDatabase 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

CohortView_v03_toshare.twbx 68.4 KB