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]

I've got it!

I needed to cheat on the expression. so it basically is a text string (not a number!)

TEXT(CEIL(AVG(AGGR(SUM(FLOOR(Duration))

+

IF(SUM(Duration - FLOOR(Duration))>'08:00:00',1,0)

,TicketNr))))

&' '&

TEXT(INTERVAL(SUM(Duration - FLOOR(Duration))- ('08:00:00'*IF(SUM(Duration - FLOOR(Duration))>'08:00:00',1,0)),'hh:mm'))

[avkeep01]

Little update, because the hours weren't an average. I needed to add COUNT(TicketNr)

and i fixed the result being a text:

(CEIL(AVG(AGGR(SUM(FLOOR(Duration))

+

IF(SUM(Duration - FLOOR(Duration))>'08:00:00',1,0)

,TicketNr))))

+

((SUM(Duration - FLOOR(Duration))- ('08:00:00'*IF(SUM(Duration - FLOOR(Duration))>'08:00:00',1,0)))/COUNT(TicketNr))

[Anonymous]

I'm sorry but it doesn't work properly and I'm not so skillful as you are to modify the expression on my own .

Pls have a look at the example I've attached: the sum of the hours of the three tickets is 12 and so the average should be 4...

Thks.

N.

[avkeep01]

Hi Nicola,

The numbers in Durations (2).qvw add up to 28 hours. which is 1 day, so im getting an average of 9 hours and 20 minutes which is 1 days and 1 hour and 20 minutes.

[Anonymous]

ok, the problem is that my days are made-up by 8 hours (not 24), so I need that the average is

1d 1h 30m (thus 9h 30m) + 2h + 30m = 12h / 3 = 4 h

I fear that it's so easy to understand as hard to do .

Am I wrong?

[avkeep01]

If the average is > 8 hours then use my difficult calculation, otherewise just use the average.

I calculate the total average in a seperate dimension. It was to much trouble getting it in 1 expression.

[Anonymous]

ok, thank you so much for all. 🙂

See you soon.

N.