topic Re: Average Time Calculation Issue in QlikView

Average Time Calculation Issue

Anonymous — Wed, 24 Sep 2014 17:55:59 GMT

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

Re: Average Time Calculation Issue

MarcoWedel — Wed, 24 Sep 2014 22:10:19 GMT

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

Re: Average Time Calculation Issue

Anonymous — Thu, 25 Sep 2014 10:55:57 GMT

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

Re: Average Time Calculation Issue

Anonymous — Fri, 26 Sep 2014 11:58:37 GMT

Hi Marco,

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

Kindly help me.

Thanks & Regards

Jeba

Re: Average Time Calculation Issue

Anonymous — Thu, 23 Oct 2014 10:17:59 GMT

Hi Marco,

The solution you gave me was perfect.

Thanks a lot for that

Regards

Jeba

Re: Average Time Calculation Issue

MarcoWedel — Thu, 23 Oct 2014 10:50:14 GMT

You're welcome

regards

Marco

Re: Average Time Calculation Issue

Anonymous — Thu, 23 Oct 2014 10:56:15 GMT

Hi Marco,

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

Average Calculation - Set Analysis

Thanks & Regards

Jeba