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 DatasetCalculationsTicket IDTicket Opened DateTicket Closed DateMaxReportDateTicket Closed (1 = yes, 0 = no)Days Ticket Opened to MaxReportDateTicket Age12/4/20172/7/20172/8/201714321/30/20172/7/20172/8/201719831/29/2017 2/8/20170101041/28/2017 2/8/20170111151/27/20171/30/20172/8/2017112361/22/20171/23/20172/8/2017117171/17/20171/17/20172/8/20171220
AggregationDays to closeAll relevant ticketsTickets ClosedClosing Speed017194%117382%217382%317571%417665%
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
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
