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

malradi88 — Sat, 16 Nov 2024 18:21:12 GMT

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

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

sunny_talwar — Mon, 20 Jul 2020 16:33:18 GMT

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

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

malradi88 — Tue, 28 Jul 2020 10:33:25 GMT

Thank you very much Sunny! Works perfectly.

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

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

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

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