topic Re: Help with a Set expression in QlikView

Help with a Set expression

Wed, 20 Mar 2013 15:03:25 GMT

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.

Re: Help with a Set expression

Gysbert_Wassenaar — Wed, 20 Mar 2013 15:20:14 GMT

Perhaps like this:

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

Re: Help with a Set expression

Fri, 22 Mar 2013 09:17:18 GMT

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?

Re: Help with a Set expression

Gysbert_Wassenaar — Fri, 22 Mar 2013 09:52:14 GMT

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.