2020: Week 13 - Solution
Our solution for this week can be downloaded from the Preppin' Data Tableau Forum.
This week we focused on improving our IT department performance by having a better understanding of SLA rates. We were given two sets of data, the Ticket Information from Jan-Mar and also the SLA agreements with various departments. Our task was to manipulate the data so that we could answer some questions as a result.
Step 1 - Input Ticket Information Data
Our first step was to input the three files of ticket information. As these all have a similar naming convention, we are able to use the wildcard union to input all three files whilst only using one input tool. It has the following setup:
 |
| Wildcard Union - Exclude all sheets starting with 'SLA' |
Step 2 - Clean Ticket Field
Within our original data source the Ticket field contains information about the Ticket ID, Department, and also who submitted the ticket. We want to separate these out into different fields, and can do this by using the Split functionality.
We need to use a custom split with the following setup:
 |
| Custom Split - Split all values using '/' as a separator |
Once we have split these into the different fields, we are now ready to join this to the SLA Agreement table. This can be done using an inner join, and using the following setup:
 |
| Inner Join - Join on 'Department' |
We now have one data set and can move onto the next step of answering the business questions!
Step 3 - Calculate Logged & Current Status Timestamps
This next step is probably the most tricky part of this week's challenge. We need to find out how long each case has been open for, but only want to count the working days that are Monday to Friday.
The first step for this is to understand how the 'Weekday' function in Tableau Prep is calculated. As standard, Tableau Prep uses the following for weekdays:
Now we understand what the 'weekday' function is going to return, we need to manipulate this slightly so that we are able to roll back the following dates:
- Any tickets logged on the weekends Monday 00:00
- Any tickets closed on the weekends back to Saturday 00:00 (Midnight Friday)
The first calculation needed is where we are going to change the weekday value:
Day of Week
DATEPART('weekday', [Timestamp]) % 7
Here we use the Modulo function to roll Saturday from 7 to 0, so the new weekday value will look like this:
 |
| Updated Weekday No. |
We can then use this updated value to roll back our dates. We create an updated timestamp using the following calculation:
Timestamp
IF [Day of Week] < 2
THEN DATE(
DATEADD('day',- [Day of Week] ,[Timestamp])
)
ELSE DATE([Timestamp])
END
This calculation rolls all weekend timestamps back to Saturday 00:00 (Midnight Friday). As we do not count weekend days, then rolling forwards to Monday 00:00 would return the same output.
Also, using DATETIME(DATE()) is a quick way of truncating any given DateTime to 00:00.
Finally we can now remove the Day of Week field as we have now updated our Timestamp field.
Now that we have manipulated our timestamp to reflect our business days, it is time to calculate how long each case has been open for. Each time the status updates, there is a new row created within the data set. Therefore we can't just do a Datediff calculation quite yet as we don't have two dates on the same row. As a result, we can use the new Analytical Calculations to find the 'start' and 'end' dates for each ticket.
First we want to find the date when the ticket was logged. We can do this by the following calculation:
Logged Datetime
{Fixed [Ticket ID] : MIN([Timestamp])}
This will return the minimum Timestamp for each Ticket ID, therefore giving us the Timestamp when the ticket was logged.
Next we need to find out what the current status of the ticket is, as some of the cases are still open and some are still closed. Now, we could use a similar technique as when finding the logged date, however we need to be careful as we have manipulated the timestamp, therefore we could have multiple Status No.s with the same Timestamp.
For example, if a ticket has been updated twice over the weekend then we would have rolled both of these rows back to Saturday 00:00, and we then have no control over what Status No. will be returned.
Therefore, instead of using Timestamp, we are going to use Status No. within the following calculation:
Current Status No
{ FIXED [Ticket ID] : MAX([Status No.]) }
This now returns the latest status no. for each Ticket ID. We can then use the following filter to remove any rows that aren't the current status:
[Status No.] = [Current Status No]
We are now left with a single row, with the logged timestamp, and also our original timestamp which is effectively the latest time that the ticket was updated. Now we have both of these dates on the same row, we can calculate the length of time a case has been open.
Step 4 - Calculate Working Days
The next step is to calculate how many weeks are between our Logged Date and Current Status Date. To do this we use the following calculation:Logged Week Number
DATEPART('week', [Logged Datetime], 'monday')
Last Update Week Number
DATEPART('week', [Last Update], 'monday')
Note, we need to make sure that the start of the week is on a Monday.
Next we want to calculate how many weekends have passed in this period:
Weekends Passed
[Last Update Week Number] - [Logged Week Number]
Then finally calculate how many days each ticket has been open for:
Days Open
(DATEDIFF('minute',[Logged Datetime], [Last Update])
-
[Weekends Passed]*2*1440
) / 1440
As we need to calculate how many days a case has been open down to the minute, we first need to calculate how many minutes between our two dates. Then we want to subtract any weekends, we multiply by two for each of the weekend days, then multiply by 1440, as there are 1440 minutes in a day. Finally, we need to divide the answer by 1440 to convert this into days.
Step 5 - Answer Questions
The final step for this week's challenge is to answer the business questions that are required. This can be done in three different branches:
1. How many cases in each status?
To answer this we can use an aggregation tool, with the following setup:
To answer this we can use an aggregation tool, with the following setup:
 |
| Group By Status Name, Count Distinct Ticket ID |
The first step to answer this question is to calculate if a ticket has breached their SLA or not. To do this we can use the following calculation:
SLA Breach?
[Days Open] > [SLA Agreement]
From here we can use the Grouping feature to group all status names, that aren't 'closed', together and rename them as 'Open'.
Next, we use an aggregation tool to count the number of tickets with their Status Name and SLA. We can use the following setup:
 |
| Group by SLA Breach & Status Name. Count No. of Rows. |
Within this aggregation, we also want to filter the results so that we are left with the following:
- Status Name - Open & SLA Breach? - False
- Status Name - Closed & SLA Breach? - True
The final step is to remove any unwanted fields, then rename the fields/values so that they match the output.
3. What departments have the best SLA achieved %?
The first step is to filter to only 'Closed' cases. So we create a new step, then Keep Only values with a Status Name of 'Closed'.
We can then use another Aggregation tool to group the SLA Breach? & Department fields, then again sum the No. of Rows:
 |
| Group by Dept & SLA Breach?. Sum No. of Rows |
We then need to Pivot the SLA Breach? field by aggregating the No. of Rows. This is the setup:
 |
| Rows to Columns Pivot |
After the pivot there may be some 'null' values in the True/False columns. We will replace these nulls with '0' when we calculate the Achieved % with the following calculation:
Achieved %
ZN([false])
/
( ZN([true]) + ZN([false]) )
Note, we use the ZN() function to make sure that all Nulls are replaced with 0's
The final step of this weeks challenge is to calculate the ranking for each department. Again this will use the Analytical Calculations feature within Tableau Prep with the following setup:
 |
| Dense Rank on Achieved % |
Outputs
Output 1
Output 2
Output 3
Make sure to fill in the participation tracker, and post you solutions onto our Tableau Forums community page so that we can compare our workflows!
