-
1. Re: Get Open Ticket Ages by Month for Past Months
swaroop.gantela Aug 16, 2018 4:00 PM (in response to David Matthews)David,
Below is a canonical method to get the result.
However, it involves a cross join which may not
be feasible if your true data set is too large.
The method is described here:
CROSS JOIN with Tableau's join dialog
This actually probably works better with your original datasource
where the open and closed dates were in the same row.
It joins your data table to a lookup table
with one column of month starts and another of month end.
It runs continuously from 1/2013 to 12/2019.
I joined the data to the lookup table using
DATETRUNC('month',[Created Date]) <= DATETRUNC('month',[Month Start])
(this was done to reduce at least some of the joins
could be done the other way with Close and End, not sure which reduces more)
Then I use a Filter on the worksheet to weed out those joins after the close month:
DATETRUNC('month',[Date Closed])>=DATETRUNC('month',[Month End])
So Ticket Status at End of Month is:
IF [Date Closed]>[Month End] THEN "Open"
ELSEIF [Date Closed]<[Month End] THEN "Closed"
END
And Ticket Age at End of Month is:
IF [Date Closed]>[Month End] THEN
DATEDIFF('day',[Created Date],[Month End])
END
Please see workbook v10.5 attached in the Forum Thread.
Here is a more comprehensive discussion on a similar subject:
Calculating ADC in Tableau Server Professional Edition 10.5.2
Possibly Pertinent links:
Vizible Difference: Taking Stock with Start and End Dates
https://redheadedstepdata.io/lookup-vs-transactional/?content=Reference%20Post
-
279122tickets.twbx 607.3 KB
-