# 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.

Tags (5)
1 Solution

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

## Re: Set Analysis and Count Distinct Expression

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

Honored Contributor II

## Re: Set Analysis and Count Distinct Expression

You can try this for 2009

And then this for 2010

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.

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.

Honored Contributor II

Like this

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

## 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.