Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Thanks Alan for your help, I am looking for something similar to the below expression from 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.
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
You say the answer must be 2
But there are 3 Distinct EmpID that had a Grade B between 2009 and 2010
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.
Maybe something like this?
=count( distinct aggr( if( count({<Year = {">=$(FromPeriod)<=$(To)"}>}distinct Year) = 2, EmpID),EmpID,Grade))
This is perfect, thank you very much Swuehl.
Will keep you posted if I find any issues.