Sum hours only for unique records

[a_ligtvoet]

Hi,

I've been reading a lot of discussions, but I still can't find the answer I'm looking for.

Or maybe I did, but I just didn't understand

In the current hour report (I can't change this) we get duplicate records if someone has different reasons for his days off.

For sick, vacation or other absence reasons the Norm_hours are mentioned every time.

Simplified dataset for 2 employees for the first 2 weeks of 2017:

- Employee 1111 works 40 hours per week (Norm_hours), Jan 4th he works 15 minutes longer (quarter of a hour is shown as 0.15 in the datadump)

As of Jan 10th he gets sick during the day, and this is where the duplicates get created.

2 records (rows) are created for the hours he worked and when he is absent, but the Norm_hours is mentioned both times.

- Employee 2222 works only 30 hours per week (6 on Tuesday on not on Wednesday).

She has some extra hours left from last year which she will use first. Also here you see 2 records for Jan 3rd with the Norm_hours mentioned both times.

Employee, Norm_hours, Hours_worked, Reason, Hours_absent, Date

1111,8,8,,,02-01-2017

1111,8,8,,,03-01-2017

1111,8,8.15,,,04-01-2017

1111,8,8,,,05-01-2017

1111,8,8,,,06-01-2017

1111,8,8,,,09-01-2017

1111,8,4,,,10-01-2017

1111,8,,SICK,4,10-01-2017

1111,8,,SICK,8,11-01-2017

1111,8,,SICK,8,12-01-2017

1111,8,,SICK,8,13-01-2017

2222,8,,TVT,8,02-01-2017

2222,6,,TVT,4,03-01-2017

2222,6,,VAC,2,03-01-2017

2222,8,,VAC,8,05-01-2017

2222,8,,VAC,8,06-01-2017

2222,8,8,,,09-01-2017

2222,6,6,,,10-01-2017

2222,8,8,,,12-01-2017

2222,8,8,,,13-01-2017

I think it may involve the aggr, sum and distinct function, but can't get it exactly right.

While I'm typing this question I also see I have to resolve the notification of 15, 30 or 45 minutes as 7.15 - 6.30 or 5.45.

I didn't take a look at that yet, but if you know how to resolve this and you can put the answer in here also, that is much appreciated.

I want to calculate the sick leave percentage.

I need to have a sum of the Hours_absent when the Reason is SICK and devide it by the Norm_hours without the duplicates in red.

Duplicates in Employee and Date should not be in there twice (the complete record is not a duplicate as it has different reasons and/or hours).

So I want to see the number 28/140=20%

I hope it's clear.

Thanks in advance!

[Oleg_Troyansky]

Hi Adam,

You are correct, you need to use AGGR() in a nested aggregation formula - first, calculate min(), max(), avg(), or only() of the repeating values, and then sum them up. Something along these lines:

sum(

    AGGR(

              min(Hours)

              , Employee, Date, Reason)

)

Confused about AGGR()? Come to the Masters Summit for Qlik and listen to my lecture on AGGR() and advanced Set Analysis! We will be in Munich this April and in Boston in September.

cheers,

Oleg Troyansky

Check out my book QlikView Your Business - the Expert Guide to QlikView and Qlik Sense.