Forum Discussion
Calculation between 2 dates based on the same value
There's no task.expired_from_workbin in your data, only task.expired_from_step??
Had a quick look (see it all at work in the attached file). The measure seems to work fine for cases like ID
541fe1360f4ca114df0000bc
but is slightly off at ID
56e1917d7765624c6b0001fd
because task 5fcfa098bd0e54813b1340de appears in two instances ( taken-completed, resume-completed)
and yields negative numbers at cases like ID
4fc675c20f4ca11924008111
where the code works well but the data only has a task.taken for taskID 5fd0b96871336830f56f98f2, which messes the result up
Dealing with those cases will be trickier but the code can give you a good start. In any case you will need to exactly define all of the possible cases in which the data can appear and, equally important, define how to deal with them
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Yes it's normal. It is task.expired_from_workbin but it is not present in the example file.
The common point must be task_id and not user_id. The user_id is a filter.
Hello @AIB,
Thank you for your help.
I have another question.
How do I make it take into account only the next action of the same task (task_id)?
Indeed, the 4 lines are the same tasks. Line 318 must go with line 376 and line 1202 with 2174.
With the given formula (and I thank you once again), calculate the time between line 318 and 2174, i.e. 155 minutes instead of 79 minutes (7 + 72).Thanks in advance.
Mathieu
Hello,
I advance a little bit ...
I managed to get the calculation of the first part but I cannot sum the 2 parts.
Namely from 8:44 am to 8:51 am + 10:08 am to 11:20 am.
7 + 72 min
How to do ?
Here is the new file.
Thanks in advance.
Mathieu
Fichiers: https://www.dropbox.com/sh/61ae2dtbmobl8tp/AAATpy0sRBMGRziU0Xe7Sy_Za?dl=0