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

[malradi88]

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 

[sunny_talwar · Accepted solution]

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]

Thank you very much Sunny! Works perfectly.