Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis and Count Distinct Expression

In the attached pivot I would like to create a expression that counts by grade employees present in both time periods and with the same grade in both periods.

For grade B the correct answer should be 2.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe something like this?

=count( distinct aggr( if( count({<Year = {">=$(FromPeriod)<=$(To)"}>}distinct Year) = 2, EmpID),EmpID,Grade))

View solution in original post

16 Replies
rustyfishbones
Master II
Master II

So do you need to see 2009 in one column and 2010 in another

rustyfishbones
Master II
Master II

You can try this for 2009

2009.png

And then this for 2010

2010.png

Not applicable
Author

No I would like to see one column that counts distinct empID's with same grade in both time periods.And I want to add the grade dimension to it.

So right now for grade B it is counting EMP ID  1,2 and 3 when it should be counting only EmpID 1 and 3 that fits the above criteria.

Thanks for your help

Not applicable
Author

The condition I want to apply in the expression is employees present in both time periods and employees with same grade in both time periods.

rustyfishbones
Master II
Master II

Like this Table.png

Not applicable
Author

Yes so in my pivot I want to create an expression that counts only EmpID 1 and 3 and excludes EmpID 2 when the dimension is Grade - B

Thanks again

swuehl
MVP
MVP

Set analysis is evaluated once per chart, not per dimenion line, so considering the dimension value of Grade in your set expression is not feasible.

You can try something like

=count(distinct aggr( if( count(distinct Year) = 2, EmpID),EmpID,Grade))

Not applicable
Author

Thank you Swuehl, however my data model runs into multiple years and I would like to include the input variables in the expression and try calculating the same expression for different time period selection from the front end.

Thanks again for your help.

rustyfishbones
Master II
Master II

Sorry, I am not a pro, do you need it like this

Test.png

If so, I used the following

DISTINCT_YEAR.png