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.
Solved! Go to Solution.
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
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))
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.