[From Wrike] Using the Days Difference (DDIFF) function
Hi Community,
I hope everyone has been well. I will continue this week and next with two posts, one showing the use of a function and another a use case that builds upon it. We will start with the often-requested DDIFF function!
You might find this piece helpful if:
- You would like an easy way to hold assignees accountable based on dates
- You would like exposure to or experience in formula edit mode
The DDIFF function calculates the number of days difference between one date field and another. Imagine subtracting one date from another and getting the difference in days. You can use DDIFF with Task or Project Start and End/Due dates, any custom date field, and you can also compare to today's date.
The most common use case is to measure the number of days from a task's due date until today to help keep track of task deadlines. Let's build a widget that does this together.
We'll start with a standard pivot showing tasks including Task Link, Task Name, Task Assignee, Task Status and Task Due Date (days grouping). For the sake of simplicity, I'm going to filter to show only Active tasks, so this dashboard will tell us about tasks that are in progress and, if late, would need immediate attention. I'm also only going to show tasks that have a due date as backlog tasks have no concept of lateness.
Next, I will add a value, which will become the number of days that have passed since the task's due date.
Let's start with a DDIFF function containing the Task Due Date (days grouping) as both arguments. Press OK and you'll see that the column shows 0 for all tasks, which is what we would expect as the two dates are the same.
Now, let's add the magic. I'm going to surround the FIRST Task Due Date with a NOW function, which tells Wrike Analyze to ignore the actual value in the Task Due Date field and instead return the current date. Ensure both fields use a days grouping.
Save the function and rename it Days Late. You may have to scroll right in the widget depending on how wide your other columns are. You should see that:
- Some numbers are positive (today is later than the due date = LATE)
- Some numbers are negative (today is earlier than the due date = NOT LATE)
- Some numbers may be zero (today is the same as the due date = NOT LATE BUT DUE TODAY)
Taking this one step further, let's add conditional formatting to define levels of "late-ness" as a compliance metric. Let's say that this metric has 4 levels:
- Not late (<= 0 days late) is Green
- Less than 7 days late is Yellow,
- Less than 14 days late is Orange, and
- At least 14 days late is Red.
Finally, sort by the "late-ness" metric field and we're done!
----------------
Looking for even more challenges with this function? Stay tuned for my next post where we will build a more complex use case using the DDIFF function!
For another challenging exercise, replicate the above color chart but pair DDIFF with a CASE function to state the level of "late-ness" in defined gradations such as: 0 (not late), 1 (<7 days late), 2 (<14 days late), 3 (>= 14 days late). Hint: You will call DDIFF 4 times!
Best wishes growing with Wrike Analyze!
Sam Alter Community Team at Wrike Wrike Product Manager Aprenda sobre as funcionalidades e melhores práticas em Wrike com o nosso Treinamento Webinar Online
Sam Alter Wrike Team member Aprenda sobre as funcionalidades e melhores práticas em Wrike com o nosso Treinamento Webinar Online
Please, make this coding-functionallity available for business plans also, e.g. for widget filters or automation!
Thank you for sharing your feedback Florian Kislich🙌🏼
Ditto, please make this available for business plan.
Definitely love the potential here!
Hi Aloi Calvert and Sherrie Besecker, thank you both for sharing your thoughts here🙌🏼
Hi, I am trying to output total number of days between creation and closure dates. The function is giving me strange results. What am I doing wrong ?
Hi Antoine Lovergne, I've asked my colleagues from the Support team to reach out to you about this as they needed to troubleshoot with you. Looks like it's resolved now 🙂
Please let me know if we can help you with anything else!
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
Hi!
I'm trying to create the same pivot but when I create formula I obtain this error
Here my formula
Where I'm wrong?
Hi Pietro Poli, I see that our Support team is assisting you with this question. Please let me know if I can help you with anything else.
Hi @... I resolved creating a new dashboard.
Great article Sam Alter
Is it possible to count only weekdays using the DDIFF function? I'd like to exclude weekends from the day count in my calculations.
Hi Sam Salehpour, thank you for chiming in. I've asked one of our experts to assist you with your question, you'll be contacted shortly 👍
Great article here Sam Alter!
I have a question and would like to know how can I include conditional colour bar options, also can you please let me know how to consider weekdays in the formula
Hi Tushar Harrison! Weekdays can be used in filters in the Utilization & Performance data model, but Wrike Core allows reporting on calendar days only. As for color coding, you can do that for formulas only: you can apply some color to the output based on some condition. The best example is probably the template widget "Project List": conditional color coding is applied to the formula "% Task Completion". You can use the same approach with any formula:
I'd also like to mention that the first condition is always stronger. If some of your conditions may overlap, the one with the smallest output should go first. In the example above, the condition <0.66 goes after <0.33. In this case, only the values that don't match the first condition and are larger than 0.33 will be assessed by the second condition.
Herine Community Team at Wrike Wrike Product Manager Aprenda sobre as funcionalidades e melhores práticas em Wrike com o nosso Treinamento Webinar Online
Herine Wrike Team member Aprenda sobre as funcionalidades e melhores práticas em Wrike com o nosso Treinamento Webinar Online