Average Number of Months Worked Per Year

[malradi88]

Dear Qlik Community,

I am currently trying to calculate the average number of months worked per year. I believe these are the relevant fields in my dataset:

[Year]

[Month]

[Employee]

[Salary]

I cannot seem to figure out the right expression that reads:

'average number of months worked in 2015 only counting employees that earned greater than 0 in 2015'

I tried this but no luck

(avg ({$<[Employee]= {'=sum ({<Year={2015}>}[Salary])>0'}>} [Month]))

Your help would be much appreciated.

Best,

Mohammed

[MK_QSL]

What is Average number of months worked?

Can you provide sample data or test application to work?

[effinty2112 · Accepted solution]

Hi Mohammed,

Try:

Count({$<Year = {2015}, salary = {">0"}>}Month)

in a chart with Employee as dimension. In Expressions tab set Total Mode to Avg.

Or

=avg(Aggr(Count({$<Year = {2015}, salary = {">0"}>}Month),Employee))

in a textbox to give the overall figure.

Without data to work on I'm not able to double check these. Hope this helps

Regards

Andrew

[malradi88]

Thank you Andrew and Manisha for your support. This one worked:

=avg(Aggr(Count({$<Year = {2015}, salary = {">0"}>}Month),Employee))

Thanks a lot!