topic Re: Avg count with If condition in QlikView

Avg count with If condition

Anonymous — Tue, 23 May 2017 17:52:41 GMT

Hi,

I am very new to Qlik and looking to resolve the below problem.

I need to calculate the average number of shifts per employee for employees who have done more than 5 shifts by Type.

Table is as:

StaffID,Type,HoursWorked

1,A,5

1,A,7

1,A,3

2,A,4

2,A,5

3,B,1

3,B,5

3,B,10

To get an average number of shifts per staff I used the below expression:

Avg(Aggr(Count(HoursWorked),StaffID))

To get number of staff who worked more than 5 shifts:

Count(if(Aggr(count(HoursWorked),StaffID)>5,StaffID))

And to get the average count of shifts for staff with count > 5 the below:

Avg(Aggr(count(if(Aggr(count(HoursWorked),StaffID)>5,StaffID)),StaffID))

but it does not work.

Any help will be highly appreciated.

Thanks,

swuehl — Tue, 23 May 2017 18:03:45 GMT

Maybe

Avg(Aggr(count(if( count(HoursWorked)>5,count(HoursWorked) ),StaffID))

edit: True, Sunny, it should look like

Avg(

Aggr(

if( count(HoursWorked)>5,count(HoursWorked) )

,StaffID)

)

Thanks.

sunny_talwar — Tue, 23 May 2017 18:07:10 GMT

You might have left an extra count in there.... Count(If(Count......

Anonymous — Tue, 23 May 2017 18:12:12 GMT

It worked, thank you so much Stefan.

Leszek

sunny_talwar — Tue, 23 May 2017 18:37:43 GMT

Please close the thread by marking Stefan's response as correct response