Sep 01 2021 12:39 PM
Hello,
I'm using Pivot Table and Slicer to filter data from a raw data source. Our employees work rotating shifts. Between those shifts there are overlap hours. I created a file to track their performance during those 9 hours of shifts.
Morning shift: 08:00 - 17:00
Mid Shift : 16:00 - 01:00
Night Shift : 23:30 - 08:30
I'm using Slicer to select between shifts.
Our tool provides the raw data I need without separating shifts. So for overlap hours (16:00 - 17:00) we get duplicate data resulting in employees appearing in both morning and mid shift in the Pivot Table. What I want is to Excel to ignore the second data set for duplicate entries and not include them in the pivot. Is this possible? Or is there any other way?
Am I clear enough? If not I can provide more info.
I'm attaching the excel file I'm working on for you to see what I'm doing.
Sep 01 2021 02:42 PM
Not with your dataset since it cannot actually tell which employee belongs to which actual shift, i.e Employee1 might belong to the morning shift most hours and Employee2 might belong to the mid shift most hours but because of the overlap your dataset only indicates which employees are in overlapping shifts but not actually indicate which actual shift the belong to.
Sep 02 2021 11:53 PM - edited Sep 02 2021 11:55 PM
Thank you for answering. I edited the file including shifts for each employee and made it more concise and readable. Including only 1 set of data for 1 day. Also someone else suggested me using a helper to identify overlap hours and exclude them using slicer but It's not working as intended. It's not my formula.
Sep 05 2021 12:09 AM
on the screen shot below how can anyone tell which ones the duplicates are for that employee:
Sep 05 2021 08:31 AM
Sep 05 2021 08:43 AM
Sep 05 2021 09:18 AM
Sep 05 2021 12:04 PM - edited Sep 05 2021 12:06 PM
Solution
Changed the Name 1 to Name01
Sorted the dataset by Name and No. of Initial Moderation Tasks
created filter columns rank, count, subnum, ref
did a power query on dataset including filter columns
created 2 reference queries Top 2 and Bottoms queries
filtered out bottoms on Top 2 reference query
filtered out Top2 on Bottoms reference query
cheers
Sep 05 2021 12:47 PM
Sep 05 2021 12:48 PM - edited Sep 05 2021 01:14 PM
see the bottom of my previous post
Your next issue would be how to distribute the bottoms to the top 2 and which the bottoms belong to
what formula did you use for column Avg. Handling Time(s)
Sep 05 2021 01:21 PM
Sep 05 2021 01:24 PM
Sep 05 2021 01:34 PM - edited Sep 05 2021 02:07 PM
the logic is:
rank column = summed up employee01's No. of Initial Moderation Tasks then got the ratio of each Initial Moderation Tasks to get the % so it can be ranked
=Table1[@[No. of Initial Moderation Tasks]]/SUMIF(Table1[Name],AB2,Table1[No. of Initial Moderation Tasks])
count = get the count of instances of each employee so each row can be assigned a subnumber so then the top 2 can be separated from the bottom Ns
some employees have 1, some have 2, some have 3, some have 4, some have 5 and some have 6 instances
So in order to get the top 2 I filtered out the subnumber 3,4,5, and 6
and vice versa for the bottoms
The Top 2 are the actual shifts
The Bottoms are the overlaps
To figure out the distribution of the overlaps,
add all the No. of Initial Moderation Tasks in the Bottoms Sheet which will be the 100%
Add the Avg. Handling Time(s) in the Bottoms sheet then divide it by 60 seconds
Amendment:
Add the Avg. Handling Time(s) in the Bottoms sheet then divide it by 60 minutes
then divide the sum of No. of Initial Moderation Tasks in the Bottoms Sheet
by the quotient Avg. Handling Time(s) in the Bottoms sheet
So now you have the No. of Initial Moderation Tasks in the Bottoms Sheet per minute
Multiply it by the overlap Hours/Minutes to get the distribution for each shift
then calculate ratio the shift hours:
Morning shift: 08:00 - 17:00
Mid Shift : 16:00 - 01:00
Night Shift : 23:30 - 08:30
Morning to Mid Shift is 1 hour
Mid Shift to Night Shift is 30 minutes
Night Shift to Morning Shift is 30 minutes
Sep 05 2021 02:02 PM
Sep 05 2021 02:07 PM
Sep 05 2021 02:17 PM
The bottoms calculation for the distribution is just an average ballpark since that is what your current dataset allows (an educated guess), unless you have a more granular dataset which specifically gives you the actual No. of Initial Moderation Tasks in the Bottoms Sheet stats for each shift.
Sep 05 2021 02:30 PM
Sep 16 2021 05:17 PM
Sep 05 2021 12:04 PM - edited Sep 05 2021 12:06 PM
Solution
Changed the Name 1 to Name01
Sorted the dataset by Name and No. of Initial Moderation Tasks
created filter columns rank, count, subnum, ref
did a power query on dataset including filter columns
created 2 reference queries Top 2 and Bottoms queries
filtered out bottoms on Top 2 reference query
filtered out Top2 on Bottoms reference query
cheers