Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for
Did you mean:
Highlighted Creator

## 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

Labels (3)

• ### Set Analysis

1 Solution

Accepted Solutions
Highlighted MVP

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))``````
2 Replies
Highlighted MVP

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))``````
Highlighted Creator

Thank you very much Sunny! Works perfectly.  