[From Wrike] Using DDIFF: On-Time Compliance SLA Tracking
Hi Community,
As promised, this second post expands on the DDIFF function with a common use-case: On-Time SLA Compliance Tracking!
You might find this piece helpful if:
- You would like an easy way to hold assignees accountable based on dates
- You would like an example of multi-pass aggregation in action
- You would like to challenge yourself with a more advanced use case
Hint: Saving between each step and/or duplicating widgets or values can help you keep track of your changes as you build out your widget!
Let's start with a standard pivot showing Task Link, Project Name, Task Name, Task Status, Task Due Date (days grouping), and Task Completion Date (days grouping). For the sake of simplicity, I'm going to filter to show only Completed tasks so that this dashboard will be strictly retrospective. I'm also only going to show tasks that have a due date as backlog tasks have no concept of lateness.
We'll measure the difference between the Task Due Date and Task Completion Date to determine how compliant we are against our SLA. Using the skills learned in my last post, we'll use DDIFF to measure the number of days from the Task Due Date to the Task Completion Date. Make it so that positive numbers denote that the Task was completed late, which means that the SLA was violated. Check your work.
Next, we'll use an IF statement to convert the number into a Yes/No SLA Compliance metric. A value of 1 will mean the SLA was met, whereas 0 will mean the SLA was violated. Note the addition of a MAX function--needed for the formula to compile but with no effect on the outcome of our logic. Check your work.
Add conditional formatting to illustrate the SLA Compliance metric for each task.
Good job! Now, let's take this one step further with some aggregation magic.
Duplicate your widget, calling this one SLA Compliance %
In this widget, we'll take our compliance metric that works on an individual task, and aggregate it across all tasks to calculate the percentage of those that are compliant!
Remove all the rows from your pivot so that only the value remains.
Next, switch from a Pivot to an Indicator.
We now need to modify the formula. Although this formula works on the individual task level, it doesn't know what to do when you have a bunch of tasks all at once. To upgrade our formula so that it can perform the SLA calculation in aggregate, we will use a tool called Multi-Pass Aggregation.
Step 1: Surround your formula with an aggregation function. In this case, we will use the SUM function.
Step 2: After the first open parenthesis, insert a comma.
Step 3: Before the comma, add the field to aggregate by. In this case, we will aggregate by Task ID.
Step 4: Surround your IF condition with a SUM function
This should count the number of tasks that were SLA compliant. Check your work.
Finally, add a plain count of task ID as our denominator to form the percentage.
That's two widgets, and two ways to measure compliance to an on-time completion SLA!
--------------------
Looking for even more challenges with this use case? Try visualizing SLA Compliance in different ways, such as aggregating by assignee or division, graphing over time, or making a leaderboard. There's no need to change the formula.
For another interesting exercise, make this compliance metric work for both Completed and Active tasks. Hint: Tasks that aren't completed yet should be compared to the present date.
Finally, although outside the scope of this how-to article, there are a bunch of concepts and some fascinating theory at play that make the pieces come together in this formula. Some questions to challenge yourself may include:
- Why the extra MAX function?
- Why aggregate by Task ID?
- Why does the formula not need to change when adding a grouping dimension?
- Why use a SUM function when we're trying to COUNT tasks?
As always, your Professional Services team stands ready to assist on these and many more advanced topics.
Best wishes growing with Wrike Analyze!
Sam Alter Community Team at Wrike Wrike Product Manager Conosci le straordinarie funzionalità di Wrike e le best practices
Sam Alter Wrike Team member Conosci le straordinarie funzionalità di Wrike e le best practices
Can you help? Answer the question and work your way towards becoming a Wrike Ninja 🥋 Sign in and answer.
Accedi per aggiungere un commento.