1 of 1 people found this helpful
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
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])
WINDOW_Max(if max([Sample (TestSampleData)].[Days Ticket Opened to MaxReportDate 2])
>=min([Days to close]) then 1 else 0 end)
if window_min(min([Sample (TestSampleData) (copy)].[Ticket Age]))
<= min([Days to close])
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
[All Relevant Ticket]
ifnull([All Items] -window_sum([Calculation1],first(),-1),[All Items])
([Calculation12])/([Calculation2])([Ticket Closed])/([All Relevant Ticket])
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.
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!
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.
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..
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!
CohortView_v03_toshare.twbx 68.4 KB