topic Set Analysis, count distinct in QlikView

Set Analysis, count distinct

Tue, 28 May 2013 01:06:24 GMT

In the attached qlikview document, the time periods are given as input box providing the users the flexibility to change time periods. I already have a text box with set analysis expression that calculates emp ID that overlap in both time period. I woud like to modify by set expression to answer the following -

I need two text boxes -

First Expression - I want to count the employees present in both time periods with same grade in both periods - The result should be 1 as you can see EmpID 2 is the only one that fits this criteria.

Second Expression - I want to count the employees present in both time periods and with only positive increase in grade points - The result should be 1 as you can see EmpID1 is the only one that fits this criteria as the grade points for ID -1 increased from 10 in 2009 to 20 in 2010. In my context it essentially calculates promotions in my dataset.

Thanks a lot for your help!

Re: Set Analysis, count distinct

sivarajs — Tue, 28 May 2013 07:34:42 GMT

Check the attachment

I have edited script to find the garden point is changed over the year.If changes it will show 1,from that you can find which empid got changed

Re: Set Analysis, count distinct

Tue, 28 May 2013 14:18:27 GMT

Thank you Sivaraj, the expression is not working. This always counts 1.

In the attached qlikview document, the count needs to be 2 but it still shows 1.

Thanks for your help.

Message was edited by: eiprakash

Re: Set Analysis, count distinct

RedSky001 — Tue, 28 May 2013 16:25:35 GMT

Please see the attachment.

Message was edited by: Mark Sheraton I've attached 3.1 as I made a change to expression 2 (I wasn't returning a count as you asked)

Re: Set Analysis, count distinct

Tue, 28 May 2013 19:25:35 GMT

Thank you Mark for the expression, however for the attached test case it is not working.

I am trying to count distinct employees stayed in the same grade. So in the pivot for Grade B the count should be 2 and it is calculating as 3.

Message was edited by: eiprakash

Re: Set Analysis, count distinct

RedSky001 — Tue, 28 May 2013 21:52:36 GMT

I don't understand why you're expecting a count of 2.

For grade B there is one employee who's grade is the same in 2009 as it is in 2010 and that's empid 2.

Please explain otherwise try the expressions below.

Calculated dimension:

=AGGR(

if(only({$<Year={'$(=To)'}>} [Graden points]) = only({$<Year={'$(=FromPeriod)'}>} [Graden points]),

Grade

)

,EmpID)

Expression:

count(DISTINCT { $<EmpID=p({$<Year={'$(=FromPeriod)'}>})> * $<EmpID=p({$<Year={'$(=To)'}>})>} EmpID)

Re: Set Analysis, count distinct

Tue, 28 May 2013 21:58:23 GMT

Thanks Mark for your reply.The below expression worked for me

=count( distinct aggr( if( count({<EmpYear = {">=$(FromPeriod)<=$(ToPeriod)"}>}distinct EmpYear) = 2, PERNR),PERNR,CareerProg))