Count compared to previous year- QlikSense

[RutiTaumanRubin]

Hi everyone,

I want to create measure with the following calculate:

count of EMPLOYEES where field STATUS='ACTIVE' for this year and was 'NOT ACTIVE' for last year..

Any Advice?

Thank's!

[OmarBenSalem]

Maybe sthing like this:

count(

{

<STATUS={'ACTIVE'},Year={"$(=max(Year))"}>

*

<STATUS={'NOT ACTIVE'},Year={"$(=max(Year)-1)"}>

}

employee)

[sunny_talwar · Accepted solution]

May be this

Count({<Emp = p({<Year = {"$(=Max(Year))"}, STATUS = {'ACTIVE'}>})*p({<Year = {"$(=Max(Year)-1)"}, STATUS = {'NOT ACTIVE'}>})>}Emp)

[RutiTaumanRubin]

Thank you for your response !

it's not working..

[OmarBenSalem]

Suppose u have this:

Employee	Year	Status
1	2018	ACTIVE
1	2017	NOT ACTIVE
2	2018	ACTIVE

The result should be : 1 employee which is employee 1 because he was active in 2018 and inactive in 2017 or both employees because one of the conditions is met?

Because this works:

Count(distinct {<Employee = p({<Year = {"$(=Max(Year))"}, Status = {'ACTIVE'}>})*p({<Year = {"$(=Max(Year)-1)"}, Status = {'NOT ACTIVE'}>})>}Employee)