Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello got a table that contains the year, quarter, month, and the employee as well as a field called employee count which is 1 next to each employee
I need to create a pivot table in which I have the year, quarter, and month as dimensions
as a measure I want to count the number of employees
per month I count the employees so if the employee is available in 3 months then in each of these months I count the employee but per year I should count the employee only once
so depending on whether the table is collapsed or fully expanded I should get different counts
example JOE should be counted per month but per year or quarter I shouldn't get 3 I should get 1
check attached sample application please and help on how to fix this issue
Did you try just :
Count(Distinct EMPLOYEE)
?
works fine if the pivot table is expanded at level of month but if collapsed the count distinct will result in 3 for one employee
Am I missing something? Check the screenshots below (last column).
YES I forgot to tell you
that when I collapse to the level of the year I should get 2 instead of 3 because at the level of the year I need to read the total count of the last Quarter
I updated the QVW file
Hello @tresesco
Maybe what I wrote in the following thread elaborates more what I want
Getting value of max month in set Analysis
Unfortunately I can't access the link
I already mentioned you in that Thread