Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
malradi88
Creator II
Creator II

Count # employees earning less/more than the average (per month, quarter, year)

Dear Qlik Community,

I am currently looking to count the number of employees earning less/more than the average in a given time-period (in a bar graph). 

For average salary per month I have the following equation:

avg(aggr(sum({<Amount={'=sum(Amount) >1'}>}Amount), key, MonthYear))

To obtain the average per quarter or year I just replace 'MonthYear' with quarter or year. 

I tried counting the # of employees earning less/ more with if statements and set analysis:

Attempt 1:

count ( {< key = {" avg(aggr(sum({<Amount={'=sum(Amount) >1'}>}Amount), key, MonthYear)) > avg(aggr(sum({<Amount={'=sum(Amount) >1'}>}Amount), key, MonthYear))"} >} distinct key)

Attempt 2:

if(avg(aggr(sum({<Amount={'=sum(Amount) >1'}>}Amount), key, MonthYear)) < avg(aggr(sum({<Amount={'=sum(Amount) >1'}>}Amount), key, MonthYear)), count (distinct key))

I realise I am doing something very wrong (beyond the syntax I can't figure out how to differentiate the average salary per month to the average salary of the specific person so that teh <  or >  exclusions works)  ! I can't seem to figure this out. Any advice would be very much appreciated 🙂 

Best,

Mohammed

@sunny_talwar 

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

Are you trying this may be?

Count(DISTINCT Aggr(

If(Sum({<Amount = {">1"}>} Amount) > Avg(TOTAL <MonthYear> Aggr(Sum({<Amount = {">1"}>} Amount), key, MonthYear)), key)

, key, MonthYear))

View solution in original post

2 Replies
sunny_talwar

Are you trying this may be?

Count(DISTINCT Aggr(

If(Sum({<Amount = {">1"}>} Amount) > Avg(TOTAL <MonthYear> Aggr(Sum({<Amount = {">1"}>} Amount), key, MonthYear)), key)

, key, MonthYear))
malradi88
Creator II
Creator II
Author

Thank you very much Sunny! Works perfectly.