Average Time Calculation Issue

[Anonymous]

Dear Team,

Kindly refer the attachment.

Based on experts guidance I achieved the results till column G.Thanks a lot for that

Now when I calculate the overall time taken for the task,its not giving the expected answer as written in column H.

Kindly note that we only consider the times taken by Team1 to Team4 only.Also any team can start the task first.I would like to find the difference between the starttime and end time.All the teams(Team 1 to 4) should  have the status of either "Complete" or "Not applicable".

Could you please help me on this.

Thanks & Regards

Jeba

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

tabInput:
LOAD ID,
    Team,
    Status,
    modifiedDate
FROM [Jeba_Time.xls] (biff, embedded labels, table is Sheet1$);

tabOutput:
LOAD ID,
    Timestamp(Min(modifiedDate)) as MinDate
Resident tabInput
Where Match(Team,'Team1','Team2','Team3','Team4')
Group By ID;

Left Join (tabOutput)
LOAD ID,
    Timestamp(Max(MaxDate)) as MaxDate
Group By ID;
LOAD ID,
    Team,
    Min(modifiedDate) as MaxDate
Resident tabInput
Where Match(Team,'Team1','Team2','Team3','Team4') and Match(Status,'Complete','Not applicable')
Group By ID, Team;

Left Join (tabOutput)
LOAD *,
    Interval([Time Difference]-[Weekend Hours]) as [Overall Time(Expected)];
LOAD *,
    Interval(Days-WorkDays) as [Weekend Hours];
LOAD ID,
    Interval(MaxDate-MinDate) as [Time Difference],
    Ceil(MaxDate)-Floor(MinDate) as Days,
    NetWorkDays(MinDate, MaxDate) as WorkDays
Resident tabOutput;

hope this helps

regards

Marco

[Anonymous]

Hi Marco,

Its working perfectly.

I have one small issue.It will be great if you could help me here.

Time Difference,Weekend Hours and Time Final(Current)  are manual calculations.I missed out to menstion in my earlier message.Could you please help me to calculate the above fields with the logic I have used in the excel file.

Thanks a lot.

Regards

Jeba

[Anonymous]

Hi Marco,

Did you had a chance to look at my previous request.

Kindly help me.

Thanks & Regards

Jeba

[Anonymous]

Hi Marco,

The solution you gave me was perfect.

Thanks a lot for that

Regards

Jeba

[MarcoWedel]

You're welcome

regards

Marco

[Anonymous]

Hi Marco,

When you find time could you please help me with the below query

Average Calculation - Set Analysis

Thanks & Regards

Jeba