Time Calculation - Data Issue

[Anonymous]

Dear Experts,

Need your big help in this logic.I tried several ways and I couln't figure out the answer.

Initially the data was good and I found the answer from Massimo Grassi to calculate the time difference from "In Process" to "Complete"

But now the data is not in proper flow.Some time they are updating directly as "Completed" and then they are starting "In process" and update "Complete" twice.due to which our earlier logic is not working correctly.

Could you please guide me with the below points

1.Even though it is updated as "Completed" in the first instance if we have the second instance of "In process" it should consider that as a start and then it should take the "Complete" in the maximum date/time(if we have two complete).

2.If any team/request which is carried to the next week it should ignore some specific hours in the weekend(Friday 7PM to Monday 8 AM)these times shouldn't influence the overall time taken for the team.

Attaching the application and the sample.

Request your help.

Re: Time Calculation contd..

Thanks & Regards

Jeba

[Anonymous]

Can any one help me in this issue?

[Anonymous]

May I request some one to help me in this logic

[gmoraleswit]

I'm not sure if I understand what you need, but if you need the last modified record for each id/team/status you can make a group by and then use the same logic you already have, something like this:

Load

     ID,

     Team,

     Status,

     max(Time) as Time

from ...

group by ID, Team, Status;

Hope this helps

[Anonymous]

No,I need to find the total time taken by each team from "In process" to "Complete".I already got this one.

But my data has some issue.In some instance they are updating the "Complete" in the first instance and then they update and "In Process" and goes on.Some times they update "Complete" twice.Due to these inconsistencies the calculation is not working properly.

After acheived the above I want to exclude the weekend hours(specific - Friday 8PM to Monday 8AM) which shouldn't be calculated in the time taken.

Can any one help me on this logic

[Colin-Albert]

I think you need to resolve the data quality issues separately from the chart. Get the data correct and the chart becomes simpler.

I would guess something like Gabrilella suggested - load the data grouped by ID, Team & Status, and select min(Time) as start time where the status is In Progress as the start time, and the max(start time) where the status is completed as the End time.

The networkingdays() function can be used to remove weekends and holidays from date ranges.