Set Analysis and Count Distinct Expression

[#1]

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.

[#2]

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

[swuehl]

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.

[#4]

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

[rustyfishbones]

You say the answer must be 2

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

[#6]

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.

[swuehl · Accepted solution]

Maybe something like this?

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

[#8]

This is perfect, thank you very much Swuehl.

Will keep you posted if I find any issues.