Expression in Pivot table

[anagharao]

Hi Everyone

I have compliance data per employee ,week. and project. Example:

EmpID     WeekID     ProjectID      Compliant?      Reason

1               1               1                    Yes                    -

1               1               2                    Yes                    -

1               2               1                    No                    No time booked

1               2               2                    Yes                    -

1               3               1                    No                    Different project booked

1               3               2                    No                    Incorrect time booked

Each compliance reason has a priority

1 No time booked

2 Different Project Booked

3 Incorrect time booked

When seeing the aggregated data and week level how can i see only one reason. Note that the reason is the columns in the pivot table

          Reason     Complaint     No Time booked     Different Project booked     Incorrect time booked

WeekID

1                         1

2                                               1  

3                                                                               1

Expression being the unique employee id.  Right now i get a double count of the employee in various reasons per week.

Thanks

Anagha

[YoussefBelloum]

hi,

how do you want to see only one reason when yo have 3 weekend as a dimesion on your pivot table ?

[alis2063]

Just take distinct Emp id

[anagharao]

That is what i am currently doing. however the results are as below:

          Reason     Complaint     No Time booked     Different Project booked     Incorrect time booked

WeekID

1                         1

2                         1                     1 

3                                                                               1                                        1

[anagharao]

i want the prioirity of the reason to determine whihc one reason gets picked up for each week.

[cweiping · Accepted solution]

is this?

aggr(count( distinct EmpID),EmpID,Reason,WeekID)