Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ciara
Creator
Creator

Using variable in set analysis

I have a list of employees that have a start and end date.  They are associated with a date for each date of employment.

I've also created a variable that counts the number of days between an employees start date and any particular date during their employment.  

I want to be able to pick a month and if an employee has over 90 days of employment at any date within that month then count them.

I thought I could use a simple if statement and then I tried using my variable within set analysis but I haven't found the correct formula yet.

 

Here is my code for associating an employee with each date of their employment

Dates:
Load 
	EMPREF,
    Date(StartDateTemp + IterNo() - 1,'DD/MM/YYYY') 					as newDate,
    StartDateTemp,
	EndDateTemp
Resident EmployeeDateRange
While Date(StartDateTemp + IterNo() - 1,'DD/MM/YYYY') <= If(EndDateTemp<Today(),EndDateTemp,Today());

 

Maybe I could also include a dimension that will give me the day in numbers of their employment, Day (1,2,3 ... etc).

And then use this to ascertain if they are over or under the 90 days?

Any help is appreciated.

Thanks

Ciara

1 Reply
GaryGiles
Specialist
Specialist

I might need further clarification, but, based on your data, if you start with the expression:

=count(Distinct if(newDate-StartDateTemp>90,EMPREF))

it will give you a count of all employees to have reached 90 days of employment at anytime.  If your month field is based on the newDate field, by filtering on a month, that expression would then give you employees that were employed at some point in that month and at some point in the month, reached 90 days of services.

Or are you looking for only employees that hit 90 days in employee as of that month?