topic Simple interesection problem in QlikView

Simple interesection problem

Fri, 24 Aug 2012 10:01:04 GMT

I have a data like this

Date Count

8/21/2012 3768

8/23/2012 3769

So there are 3768 employees was in 8/21/2012 and 3769 employees are in 8/23/2012.

I just want to know who is that employee which was added on 8/23/2012.

Is it possible to do this using set analysis Interesection??

thanks

Lavi

Re: Simple interesection problem

swuehl — Fri, 24 Aug 2012 10:06:12 GMT

Try something like

=only({<EmployeeField = e({<Date = {'8/21/2012'}>})*p({<Date= {'8/23/2012'}>}) >} EmployeeField)

Use your field for Employees instead of EmployeeField and check that the date format used in the set modifiers is exactely as the format used in the Date field.

Re: Simple interesection problem

Fri, 24 Aug 2012 11:56:36 GMT

Hey thanks Swueh,

it worked. What if i want to see who has left the company? If the data is like this

Date Count

8/07/2012 3777

8/21/2012 3768

Thanks

Lavi

Re: Simple interesection problem

swuehl — Fri, 24 Aug 2012 12:09:11 GMT

The e() and p() functions are part of so called set analysis, which is kind of advanced QV stuff. There is a (small) section in the Help file about set analysis.

The e() function in the set modifier is returning Employees that have no relation / link to that date, the p() function is returning Employees that have a relation the date specified. The intersection operator * is then returning only the Employees that are in both sets (no relation to first date, but relation to second date).

So if you need to request Employees that have no relation to a date (e.g. because they quit), but have a relation to another date, just input the dates accordingly:

=only({<EmployeeField = e({<Date = {'8/21/2012'}>})*p({<Date= {'8/07/2012'}>}) >} EmployeeField)

You should also be able to use a range of dates if needed:

=only({<EmployeeField = e({<Date = {">=8/21/2012"}>})*p({<Date= {"<=8/07/2012"}>}) >} EmployeeField)

should return Employess that have a relation to dates before 8/7/2012, but have no relation to dates after 8/21/2012.