Avg count with If condition

[Anonymous]

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 · Accepted solution]

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]

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

[Anonymous]

It worked, thank you so much Stefan.

Leszek

[sunny_talwar]

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

Qlik Community Tip: Marking Replies as Correct or Helpful