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: 
Not applicable

Help with a Set expression

Hi

Can someone help me to get a set expression right.

I want to

Count({<Avgorsak ={9} >} Distinct PersonID)      but only for those that dont have a HireDate that are larger than the EndDate

It´s a stright tabel that shows how many people that have ended ther hirings and don´t have come back later.

It´s the ones that have been rehired I want to remove.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Something like:

Count({<EndDate -= {'9999-12-31'}, Avgorsak ={9},PersonID={"=aggr(max(HireDate),PersonID)<=EndDate"} >} Distinct PersonID)

You may have to use the date# function to make a date of 9999-12-13. The expression editor doesn't understand -= but it's the correct syntax.


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

Perhaps like this:

Count({<Avgorsak ={9},PersonID={"=aggr(max(HireDate),PersonID)<=EndDate"} >} Distinct PersonID)


talk is cheap, supply exceeds demand
Not applicable
Author

I think you are on the right way, but now I fund that people that are still hired are set to 9999-12-31 as enddate. In your above expression, how can I exclude that date?

Gysbert_Wassenaar

Something like:

Count({<EndDate -= {'9999-12-31'}, Avgorsak ={9},PersonID={"=aggr(max(HireDate),PersonID)<=EndDate"} >} Distinct PersonID)

You may have to use the date# function to make a date of 9999-12-13. The expression editor doesn't understand -= but it's the correct syntax.


talk is cheap, supply exceeds demand