Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

malradi88
Contributor

Average Number of Months Worked Per Year

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

1 Solution

Accepted Solutions
effinty2112
Honored Contributor

Re: Average Number of Months Worked Per Year

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

3 Replies
MVP
MVP

Re: Average Number of Months Worked Per Year

What is Average number of months worked?

Can you provide sample data or test application to work?

effinty2112
Honored Contributor

Re: Average Number of Months Worked Per Year

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
Contributor

Re: Average Number of Months Worked Per Year

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

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


Thanks a lot!

Community Browser