Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis, count distinct

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!


6 Replies
sivarajs
Specialist II
Specialist II

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

Not applicable
Author

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
Creator III
Creator III

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)

Not applicable
Author

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
Creator III
Creator III

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)

Not applicable
Author

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