Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I have data that looks something like this
Compliant In office No time booked
Employee
EMP1 1
EMP2 1
EMP3 1
.
.
The dimension is employee and Aggr(FirstSortedValue([COMPLIANCE REASON] , -[COMPLIANCE PRIORITY]), EMP_ID)
and the expression COUNT(DISTINCT EMP_ID)
This works fine. However there is time booked by these employees that i want to capture. SUM([TIME]) leads to incorrect values. It adds up all time regardless of the compliant values.
what i have
Compliant In office No time booked
Employee
EMP1 320
EMP2 320
EMP3 320
what i need
Compliant In office No time booked
Employee
EMP1 7
EMP2 10
EMP3 320
The above values are because in the 320 hrs EMP1 and EMP2 have been non compliant for 7,10 hrs only. Any ideas ?
How are you getting 7 for Emp1? and 10 for Emp2?
Emp1
Emp2
7 - Emp1 as the time to be booked is 7 on week 2 in which he has not been complaint. and no values for the the others since he has been non complaint.
10 for emp2 as time booked for "in office" is 10. the no time booked and complaint times need to be ignored here.
I still don't think I understand where you are coming up with those numbers.... can you highlight the numbers in an image to show where are getting them from?
EMP_ID WEEKID PROJ_ID [TIME TO BE BOOKED] [TIME BOOKED] COMPLAINCE_ID
EMP1 1 1 7 7 1
EMP1 2 1 7 - 3
EMP2 1 3 10 - 3
EMP2 1 6 - 10 2
EMP2 2 3 10 10 1
EMP3 1 4 6 6 1
EMP3 1 5 2 2 1
EMP3 2 4 6 6 1
EMP3 2 5 2 2 1
I've highlighted the above two rows that i 've used to derive my non compliance times