Average with working days

[Anonymous]

Hello,

I made a statistic with the performance of the assistant ticket, and I have these data:

1st ticket open 08/09/17 at 14:00 close 11/09/17 at 15:30 -> 2d 1h 30m

2nd ticket open 05/05/17 at 08:00 close 25/05/17 at 15:29 -> 17d 7h 30m

(I exclude only Sundays in my calculation)

Total duration time: 19d 9h 0m (right)

Then I need to calculate the average duration time and the AVG formula gives me (19d 9h 0m / 2)=9d 16h 30m, considering a day made up of 24 hours (right)

This result if wrong for me, because my working day is made up of 8 hours, so I have (19d 9h 0m --> 20d 1h 0m / 2)=10d 0h 30m.

How can I calculate it?

Thank you for the attention.

N.

[avkeep01]

Hi Nicola,

You could calculate it in 3 steps (replace datetimes with fieldnames):

1. Whole days:

DAYSTART(11/09/17 at 15:30) - DAYSTART(08/09/17 at 14:00)  - 1 = 2 days (9/9 and 10/9)

2. Add partial days

First day

DATETIME(DAYSTART(08/09/17 at 14:00)+'17:00:00)) -  DATETIME(08/09/17 at 14:00) = 03:00:00

Last day

DATETIME(11/09/17 at 15:30) - DATETIME((DAYSTART(11/09/17 at 15:30)+'09:00:00)   = 15.30 - 09:00 (assuming you'll start at 09:00 in the morning and make 8 hour days). = 6:30

3. Calculate > 8 hours as a day

FLOOR((03:00:00 + 06:30:00)/08:00:00)  = 1  because 3+6:30  = 09:30 hours, s it is one day,.

Calculate the remaining hours: (03:00:00 + 06:30:00)-08:00:00  = 01:30:00

Thus 2 + FLOOR((03:00:00 + 06:30:00)/08:00:00) + (03:00:00 + 06:30:00)-08:00:00 = 3 days, and 1.5 hours.

In expression it would be

DAYSTART(EndDate) - DAYSTART(StartDate)  - 1

+

FLOOR(((DAYSTART(StartDate)+'17:00:00') -  StartDate) + (EndDate - (DAYSTART(EndDate)+'09:00:00')) / '08:00:00')

+

((DAYSTART(StartDate)+'17:00:00') -  StartDate) + (EndDate - (DAYSTART(EndDate)+'09:00:00'))-'08:00:00'

[avkeep01]

Hi Nicola,

The sundays aren't excluded btw. You could use the following script for that. I created a cross join (between the dates and a calendar). There I counted the sundays.

SourceTable:

LOAD

*

INLINE [

StartDate, EndDate

08-09-2017 14:00:00, 11-09-2017 15:30:00

05-05-2017 08:00:00, 25-05-2017 15:29:00

];

// Create calendar

FOR k = 2017 TO YEAR(TODAY())

Calendar:

LOAD

DATE(DATE(DAYSTART(MAKEDATE($(k),1,1))) + RECNO()-1,'YYYY-MM-DD') AS Date

AUTOGENERATE((DATE(DAYSTART(YEAREND(MAKEDATE($(k),1,1))))-DATE(DAYSTART(MAKEDATE($(k),1,1))))+1);

NEXT k

LEFT JOIN (Calendar) LOAD

StartDate,

EndDate

RESIDENT SourceTable;

SundayCounter:

LEFT JOIN (SourceTable)  LOAD

SUM(IF(NUM(WEEKDAY(Date))=6,1,0)) AS Sundays,

StartDate,

EndDate

RESIDENT Calendar

WHERE Date > StartDate AND Date <= EndDate

GROUP BY StartDate,EndDate;

DROP TABLE Calendar;

Result:

NOCONCATENATE LOAD

DAYSTART(EndDate) - DAYSTART(StartDate)  - 1

-

Sundays

+

FLOOR(((DAYSTART(StartDate)+'17:00:00') -  StartDate) + (EndDate - (DAYSTART(EndDate)+'09:00:00')) / '08:00:00')

+

((DAYSTART(StartDate)+'17:00:00') -  StartDate) + (EndDate - (DAYSTART(EndDate)+'09:00:00'))-'08:00:00' AS Time,

StartDate,

EndDate

RESIDENT SourceTable;

DROP TABLE SourceTable;

[Anonymous]

Hi,

thank you for the prompt response.

I can't (and I don't want to) modify my single ticket calculated time, because it's the result of a complicated algorithm (not all day have the same work time, ecc, ecc), and it is like I need.

My problem is to average several days: let me to modify only a bit my question...

- 1st ticket: 2d 1h 30m

- 2nd ticket: 17d 7h 30m

Total duration time: 19d 9h 0m (right)

Then I need to calculate the average duration time and the AVG formula gives me (19d 9h 0m / 2)=9d 16h 30m, considering a day made up of 24 hours (right)

This result if wrong for me, because my working day is made up of 8 hours, so I have (19d 9h 0m --> 20d 1h 0m / 2)=10d 0h 30m.

Hope it is much clear now.

Thank you.

N.

[avkeep01]

Hi Nicola,

I got it. So I don't need to recalculte the whole time. Only correcting for the 8 hours.. You could use the last part of my calculation I would pressume.

19 days

+

19d 9h 0m - 19 = 9 hours -> FLOOR(9 hours / 8 hours ) = 1 day

+

9 hours - 8 hours = 1 hours

=

20 days + 1 hour

So assume 19d 9h 0m = [Time]

FLOOR([Time]) +  FLOOR( ([Time] - FLOOR[Time] ) /'08:00:00') +  (([Time] - FLOOR[Time] ) - '08:00:00')

[Anonymous]

ok, forgive me if I'm stressing you 😉

I've made a little example (I'm not able to create it with day and hours, so I created only with days, but it's good the same for the purpose...)

In the first calculated column I have the two partial duration time and the wrong (for my purpose) average; in the second column I tried to apply your suggestion, but the partial duration times are wrong and the average is right...

Would you be sorry to have a look at it, pls?

Thanks.

[avkeep01]

Hi Nicola,

You aren't stressing my. No worries. English isn't my native language so maybe I'm not using the correct words or intonation. It is just the other way around. I have good fun in solving this question.

I'll look at your example and try to fix it.

I just updated your numbers if you don't mind to :

415:30:00 = 17 days, 7 hours and 30 minutes

49:30:00 = 2 days, 1 hour and 30 minutes.

Are those the correct numbers?

[Anonymous]

Right, your numbers are correct (I don't know how you calculate them, but they are right ); if you replace them in my inline instruction you'll find the right partial duration and the right (based on 24h) average in the second colums; vice versa in the third column (where I applied your suggestion) you'll find both the wrong partial duration and wrong the average one.

Pls have a look to my attachment to see them.

At the end of all, I'd like to have one table with something like this:

TicketNr Duration

1              2 1:30

2            17 7:30

-------------------------

Average  10 0:30

Note that 10 0:30 is the average of (19d 9h 0m --> 20d 1h 0m / 2)=10d 0h 30m.

Have a nice evening and c u tomorrow.

Thanks a lot.

N.

[avkeep01]

Hi Nicola,

The problem is that intervals are numbers shown in 24-hours format. With my calculation I corrected for 8 hours. For example 2 days 1:30 - 8 hours = 1 day 17:30. And because 17 < 24 it keeps showing 17 hours.

And that seems to be the problem. I don't think I solved anything, at least not in the direction of showing a 24 hours time format as an 8 hour format. I need to rethink this and maybe I will break your numbers apart (days, hours and minutes).

Right, your numbers are correct (I don't know how you calculate them, but they are right );

2 days * 24 hours + 1,5 hours = 49:30:00 hours

17 days * 24 hours + 7,5 hours = 415:30:00 hours

[Anonymous]

Hello!

ok, pls consider that my partial durations are expressed in 8-hours day, so you can't find a number of hours > 8 (even if the format hh:mm allows it).

Got the calculation for the LOAD INLINE instruction 😉

Thanks.

N.