Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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
MVP
MVP

Re: Set Analysis and Count Distinct Expression

Maybe something like this?

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

16 Replies
rustyfishbones
Honored Contributor II

Re: Set Analysis and Count Distinct Expression

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

rustyfishbones
Honored Contributor II

Re: Set Analysis and Count Distinct Expression

You can try this for 2009

2009.png

And then this for 2010

2010.png

Not applicable

Re: Set Analysis and Count Distinct Expression

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

Re: Set Analysis and Count Distinct Expression

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
Honored Contributor II

Re: Set Analysis and Count Distinct Expression

Like this Table.png

Not applicable

Re: Set Analysis and Count Distinct Expression

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

MVP
MVP

Re: Set Analysis and Count Distinct Expression

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

Re: Set Analysis and Count Distinct Expression

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
Honored Contributor II

Re: Set Analysis and Count Distinct Expression

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

Test.png

If so, I used the following

DISTINCT_YEAR.png