Set Analysis, count distinct

[#1]

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!

[sivarajs]

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

[#3]

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

[RedSky001]

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)

[#5]

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

[RedSky001]

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)

[#7]

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))