topic Re: Average with working days in QlikView

Average with working days

Anonymous — Wed, 29 Nov 2017 13:15:31 GMT

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.

Re: Average with working days

avkeep01 — Wed, 29 Nov 2017 13:39:15 GMT

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'

Re: Average with working days

avkeep01 — Wed, 29 Nov 2017 14:03:12 GMT

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;

Re: Average with working days

Anonymous — Wed, 29 Nov 2017 15:17:44 GMT

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.

Re: Average with working days

avkeep01 — Wed, 29 Nov 2017 15:29:16 GMT

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')

Re: Average with working days

Anonymous — Wed, 29 Nov 2017 16:24:55 GMT

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.

Re: Average with working days

avkeep01 — Wed, 29 Nov 2017 16:34:07 GMT

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?

Re: Average with working days

Anonymous — Wed, 29 Nov 2017 16:54:56 GMT

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.

Re: Average with working days

avkeep01 — Thu, 30 Nov 2017 08:27:37 GMT

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

Re: Average with working days

Anonymous — Thu, 30 Nov 2017 08:43:56 GMT

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.

Re: Average with working days

avkeep01 — Thu, 30 Nov 2017 09:30:57 GMT

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'))

Re: Average with working days

avkeep01 — Thu, 30 Nov 2017 09:51:00 GMT

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))

Re: Average with working days

Anonymous — Thu, 30 Nov 2017 09:57:36 GMT

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.

Re: Average with working days

avkeep01 — Thu, 30 Nov 2017 10:08:05 GMT

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.

Re: Average with working days

Anonymous — Thu, 30 Nov 2017 10:23:09 GMT

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?

Re: Average with working days

avkeep01 — Thu, 30 Nov 2017 11:27:54 GMT

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.

Re: Average with working days

Anonymous — Fri, 01 Dec 2017 08:07:24 GMT

ok, thank you so much for all. 🙂

See you soon.