How to Calculate DDIFF between Status X and Complete in Analytics?
In Wrike Analytics, does anyone know how would I go about calculating the length of time from the date a task was changed to a particular status to the completed date for the completed tasks? Once our review tasks are actually started, the task goes from scheduled to a specific status. I want to calculate the DDIFF between when the task is changed to that status and when the task is marked as complete (there are statuses in between). This could also be calculated based on when it leaves a particular status.
I tried to use the DDIFF between the start and completion dates, but since we have users that actually start their projects months after the task start date (and don't update the dates) it is returning garbage data for us.
The Analytics information in the Wrike help center is bare-bones and only covers super basic items (like it lists its available functions, but not how to use them). Yes, I've had the deployment session with them as well, and honestly, I had already built more robust dashboards than anything they showed me during that training. I contacted support to be told that there is another add-on that we would need to purchase for them to tell me if it is possible to do what I want, and the formula to do so (something that would have probably taken less time than the email response I received to buy their additional training). I understand that they deserve payment for support, but they have literally zero training or documentation on how to do things in the system... they make you rely on paying them rather than being able to research it on your own.
My request to the people at Wrike -
Make training documentation for Wrike Analytics available to users who are already paying for the program.
|
|
I figured out the formula - I exported my table with completion dates and the date that the task changed to status X and used excel to give me the day difference and average. I continued to work on the formula in analytics and I have created a formula that matches the average from excel:
(SUM([Task Status Change History Time Spent (minutes)]),[Task Status Change History Old Status],[Days in Task Completion Date])/60/24 / (DUPCOUNT([Task ID]),[Task Status Change History Old Status],[Days in Task Completion Date])
Hi Ashley Fischer, thank you for coming back here and sharing the formula you've created 🙌🏼
Hi @...! Could you explain why are you passing 3 arguments in the first part of the formula? I'm looking at it and this is how I'm understanding it:
Factor 1:
SUM([Task Status Change History Time Spent (minutes)]) - This returns the time spent in the status
[Task Status Group] - No idea why is this used. How this returns a number?
[Days in Task Completion Date] - Same as above
/60/24 - Converting to a number?
Factor 2:
Returns the number unique tasks
Is that correct? Thank you!!!
Gustavo Gonzalez
1. It's not [Task Status Group], it's [Task Status Change History Old Status]. This is to filter the data the formula pulls in (only from specific task statuses). We use a "Scheduled" status when building out projects/tasks and the task might stay with that status for a month before the task is worked on. Once the task is being worked on the status will change to one of our review statuses. We are only looking for the amount of time the task was actually being worked on, so "scheduled" as well as a few others needed to be excluded.
2. The [Days in Task Completion Date] is a filter that allows me to remove any tasks with N/A as a completion date. I could have also used [Task Status Group] and filtered to only see Completed tasks. This returns the same data.
3. The only way to calculate the task status history time spent is minutes and that isn't how I want to present the data. The /60/24 gives the number of days rather than the number of minutes.
The second factor does return the unique number of tasks. This formula divides the total time in the statuses selected by the number of tasks giving me the average length of time a task takes to complete.
I hope that helps!
Hi everyone, our Solutions Architect Sam Alter recently published a post on DDIF if you'd like to take a look - you can read it here.