2024: Week 26 - Solution
Solution by Tom Prowse and you can download the workflow here.
The first step is to make sure that any complaints that are still in progress have a date set to today (26th June 2024) as their resolved date.
Date Resolved
IF [Response to Consumer]='In Progress'
THEN MAKEDATE(2024,6,26)
ELSE [Date Resolved]
END
We can then calculate how many days it has taken to solve the complaint if it was Closed:
Days to Resolve
IF CONTAINS([Response to Consumer],'Closed')
THEN DATEDIFF('day',[Date Received],[Date Resolved])
END
And also how many days it has been open for if it is still In Progress:
Days Open
IF [Response to Consumer]='In Progress'
THEN DATEDIFF('day',[Date Received],[Date Resolved])
END
We can then calculate if the response was timely or not by calculating if it was resolved in less than 14 days:
Timely Response
[Days to Resolve]<14
Then finally we can create a parameter to switch between the Closed or In Progress values for our output. For this we need to create a string parameter with the two values:
We can then use this parameter within a filter to select the correct Status:
CONTAINS([Response to Consumer],[Parameters.Status])
Finally we are ready to output the data where we can add the parameter to the output name so that we can identify which selection has been made:
The outputs should look like this:
In Progress
Closed
You can view the outputs here.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
