[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 Узнайте о самых популярных функциях Wrike и советах по его использованию
Sam Alter Wrike Team member Узнайте о самых популярных функциях Wrike и советах по его использованию
Hi Sam Alter,
great article.
I'm in a similar situation.
I've for each task this two dates (Due Date and Due Date Standard), the first is Due Date (standard field of Wrike) the second is a custom field.
I'm creating a new Pivot in Analyze to get info on:
- For each task how many days are between two dates
Till now all is ok.
Now I need to remove the 3 rows you see leaving only Assignee as unique raw.
But GG Differenza get a strange value.
So I tried to SUM task by task, but it doesn't work.
Any ideas to resolve?
thanks in advance
Claudio Mennecozzi fyi
Hi!
Maybe Herine can help me?
Hey Pietro Poli, apologies for the late reply! As Herine is not available at the moment, I've asked our Support team to look into this for you 👍
Lisa Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover
Lisa Wrike Team member Become a Wrike expert with Wrike Discover
No problem Lisa, I've resolved by myself
That's great to hear Pietro Poli! Feel free to share the solution here, I'm sure it would be helpful!
Lisa Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover
Lisa Wrike Team member Become a Wrike expert with Wrike Discover
Yes of course Lisa, I've added a MIN function before DDIFF to get for each task the exact number and sum it.
Thank you, Pietro Poli! 👏