topic Re: How to write set analysis expression (multiple conditions) with AND / OR operators? in QlikView

How to write set analysis expression (multiple conditions) with AND / OR operators?

Sun, 25 May 2014 08:04:09 GMT

My data looks like the table below:

LoadDate	EmpID	Level	Status	Wages
01-Jan-2014	100	M2	ONBOARD	10000
31-Jan-2014	100	M2	ONBOARD	10000
28-Feb-2014	100	M2	ONBOARD	12000
31-Mar-2014	100	M2	ONBOARD	12000
30-Apr-2014	100	M2	ONBOARD	12000
31-Mar-2014	120	M1	XFER_IN	13000
30-Apr-2014	120	M1	ONBOARD	13000
01-Jan-2014	130	M1	ONBOARD	12000
31-Jan-2014	130	M1	ONBOARD	12000
28-Feb-2014	130	M1	PROM_OUT	12000
28-Feb-2014	130	M2	ONBOARD	12000
28-Feb-2014	130	M2	PROM_IN	15000
31-Mar-2014	130	M2	ONBOARD	15000
30-Apr-2014	130	M2	ONBOARD	15000

I wish to write an expression that will count distinct employees who have a transaction with status "ONBOARD" on 01-Jan-2014 and 30-Apr-2014 (continuing employees) including any promoted during this period . The table dimension is grade as displayed the expected results. The expression used by me is not producing the right results.

Level	Op HC	Op Wages	Prom_In HC	Prom Wages	Post Inc HC	Post Inc Wages
M1	1	12000	-	-	1	12000
M2	1	10000	1	15000	2	27000

Advance thanks.

Re: How to write set analysis expression (multiple conditions) with AND / OR operators?

swuehl — Sun, 25 May 2014 12:47:14 GMT

Sorry, I haven't fully understood what you want to accomplish (especially looking at your second, expected table).

In general, there are set operators for Union, Exclusion, Intersection and XOR, so chances are that you can do what you want using these oeprators. Have a look at the Help for more detail or detail your requirements.

Re: How to write set analysis expression (multiple conditions) with AND / OR operators?

rwunderlich — Sun, 25 May 2014 14:18:24 GMT

I think one way would be:

=count({

<EmpID=P({<LoadDate={"01-Jan-2014"},Status={ONBOARD}>})>

*<EmpID=P({<LoadDate={"31-Mar-2014"},Status={ONBOARD}>})>

}DISTINCT EmpID)

-Rob