topic Re: Set Analysis and Count Distinct Expression in QlikView

Set Analysis and Count Distinct Expression

Tue, 28 May 2013 19:37:21 GMT

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.

Re: Set Analysis and Count Distinct Expression

rustyfishbones — Tue, 28 May 2013 20:00:08 GMT

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

Re: Set Analysis and Count Distinct Expression

rustyfishbones — Tue, 28 May 2013 20:11:00 GMT

You can try this for 2009

And then this for 2010

Re: Set Analysis and Count Distinct Expression

Tue, 28 May 2013 20:13:29 GMT

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

Re: Set Analysis and Count Distinct Expression

Tue, 28 May 2013 20:16:02 GMT

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.

Re: Set Analysis and Count Distinct Expression

rustyfishbones — Tue, 28 May 2013 20:22:54 GMT

Like this

Re: Set Analysis and Count Distinct Expression

Tue, 28 May 2013 20:25:36 GMT

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

Re: Set Analysis and Count Distinct Expression

swuehl — Tue, 28 May 2013 20:30:09 GMT

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

Re: Set Analysis and Count Distinct Expression

Tue, 28 May 2013 20:39:51 GMT

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.

Re: Set Analysis and Count Distinct Expression

rustyfishbones — Tue, 28 May 2013 20:41:25 GMT

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

If so, I used the following

Re: Set Analysis and Count Distinct Expression

Tue, 28 May 2013 20:44:03 GMT

Thanks Alan for your help, I am looking for something similar to the below expression from Swuehl.

Re: Set Analysis and Count Distinct Expression

swuehl — Tue, 28 May 2013 20:47:11 GMT

I haven't specified the Years in the expression, just the requirement to return any EmpID with same Grade in two years. So if you select a different set of years, it should work.

If I've misunderstood your requirement please update your data sample and your requested output.

Re: Set Analysis and Count Distinct Expression

Tue, 28 May 2013 20:54:19 GMT

Please find the attached, I would like to pass the input variables to define From and To time periods in which this pivot will work.

I would like to specify years in the same expression.

Thanks

Re: Set Analysis and Count Distinct Expression

rustyfishbones — Tue, 28 May 2013 21:03:14 GMT

You say the answer must be 2

But there are 3 Distinct EmpID that had a Grade B between 2009 and 2010

Re: Set Analysis and Count Distinct Expression

Tue, 28 May 2013 21:06:53 GMT

Alan - The expression should work at the grade level and count distinct employees only when the following criteria is met -

Employees should be present in both time periods and with same grade in both periods.EMP ID 1 and 3 are the only ones that satisfy the condition.

Re: Set Analysis and Count Distinct Expression

swuehl — Tue, 28 May 2013 21:07:02 GMT

Maybe something like this?

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

Re: Set Analysis and Count Distinct Expression

Tue, 28 May 2013 21:13:54 GMT

This is perfect, thank you very much Swuehl.

Will keep you posted if I find any issues.