Forum Discussion
Filtering Away Certain Duplicate Values in Pivot Data Source
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
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
- Don't get me wrong. It's not about getting a second opinion. It's about understanding the way you constructed this 🙂
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.
- Yes do consult your friend to get a second opinion
cheers - Thank you man! I'm not at the level of fully comprehending your explanation yet, however, I'll consult a friend. I'll hit you up again, if we can't figure it, of course if that's okay for you.
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 secondsAmendment:
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
- you better check your dataset since I found straggler formulas in that column by selecting the column and do a find all "="
- Many thanks man, I'm trying to figure out your logic. I shouldn't ask more of you. There is no formula for AHT. Raw data source provides that.
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)
- Man that looks awesome. Could you share the file as well?