Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Coldie
Contributor
Contributor

Find status of resource between two dates

Hi,

I want to load a table of employees who where ACTIVE between two dates (supplied by variables). The table looks like this:

  

LABORCODESTATUSCHANGEDATE
1100INACTIVE2018-08-17 11:00
1100ACTIVE2014-01-01 12:00
2070ACTIVE2018-09-03 11:00
2070INACTIVE2018-07-30 14:34
2070ACTIVE2013-11-12 09:10

(Data format of CHANGEDATE is YYYY-MM-DD hh:mm)

For instance, how do I return the laborcodes of employees that where ACTIVE between 2018-08-01 and 2018-08-31?


Note: the employee only has to be active part of the between-dates to be considered ACTIVE. I.e. in the above example laborcode 1100 should be loaded since he was active part of August 2018, but not laborcode 2070.


Thanks

Peter

1 Solution

Accepted Solutions
swuehl
MVP
MVP

You can do similar what Henric shows for rates:

Creating a Date Interval from a Single Date

Then create reference dates for the intervals

Creating Reference Dates for Intervals

Now you can filter the august 2018 dates and status active and this should return the valid codes.

View solution in original post

6 Replies
Michiel_QV_Fan
Specialist
Specialist

What is the relation between the table shown and the employees? Does the employee table also have the LABORCODE?

Coldie
Contributor
Contributor
Author

Yes, the link between the status table and the employees table is LABORCODE.

The employees table basically contains LABORCODE and NAME.

boraste-sagar
Contributor III
Contributor III

hi,

if we store start and end date end of the period in 2 diff variables and compare them in the set analysis with status=Active it should work

ex if i have change_date as todays date and status =active for the period of 1 aug-2018 to 31-aug-2018 then

({<status={'active',change_Date={">=$(=Floor(MonthStart(Today(),-1)))<=$(=MonthEnd(Today(),-1)))"}>}

Coldie
Contributor
Contributor
Author

Hi,

Unfortunately that wouldn't work since the expression only will find people who's status has changed to active during august 2018. It wouldn't find people who e.g. have been active since 1 jul-2018.

I want to find all employees who are active during, for instance aug-2018, regardless when they where activated.

swuehl
MVP
MVP

You can do similar what Henric shows for rates:

Creating a Date Interval from a Single Date

Then create reference dates for the intervals

Creating Reference Dates for Intervals

Now you can filter the august 2018 dates and status active and this should return the valid codes.

Coldie
Contributor
Contributor
Author

Thanks, exactly what I was looking for!

/Peter