Pivot table summary to get most common result (Mode) in a group

[P0lar]

Hi,

I have a set of schedule data which includes each employee's team name and their start time. Some teams include people who have a mix of start times, others include just people with the same start times. I'd like a way to summarise the most common start time in each team and I was trying to do this using a pivot table grouping by team name.

The problem is that I can't use the array formula MODE() as a custom summary calculation as array formulas aren't allowed in pivot tables according to the help file. I'm using Excel 2010.

Does anyone have any suggestions?

 

[Jerry Sullivan]

Hi and Welcome to the Board,

One workaround in lieu of a Mode function would be to place StartTime Field in both the Row Labels area and Values area of the Pivot.

In the Pivot Table Field List, first select the StartTime field to place it in the Values area. Summarize the Values by "Count of" if it hasn't already defaulted to that.
To get the same field in the Row Labels area, hold down the Ctrl Key then in the Pivot Table Field drag the StartTime field into the Row Labels area.

Next add your TeamName field to the RowLabel area dragging it to the first position (above StartTime).

Lastly, right-click on one of the Start Times displayed in the PivotTable Report > Filter > Top 10

Select from the Top 10 Filter Dialog Box drop downs to read:
Show | Top | 1 | Items | by | Count of StartTimes
For the Values, chose summarize by "Count" (if it hasn't already defaulted to "Count of Start Times").

More elegant alternatives would be to use an SQL Query or DAX formulas through PowerPivot, but those are a bit more complex.

 

[P0lar]

Thanks Jerry,

That helps but predictably it's created some more problems to overcome! If someone is off on a given day, then they don't appear in the source data for that day as there's no shift information for them. The most common result for the team on a given day could be a day off even if a few of the team members are working. Is there a way I can get the number of people per team and the day of the week into the above pivot table?

 

[Jerry Sullivan]

Could you post a small screen shot showing a few rows of your source data and another with your desired result?
In my signature block, there are some links that explain some options to post screen shots.

I'm still learning SQL, so if it can't be done with a PivotTable, that will be good practice to figure out how to do this through a query.

 

[P0lar]

Hi,

Apologies, I can't post the source data directly, but the format is as follows:

Name ; Team ; ShiftDate ; Day ; StartTime ; EndTime

Bob ; Janice ; 31/01/2012 ; Mon ; 09:00 ; 17:00
Jim ; Janice ; 31/01/2012 ; Mon ; 09:30 ; 17:30
Barney ; Maggie ; 31/01/2012 ; Mon ; 09:00 ; 16:30

etc...

 

[Jerry Sullivan]

Thanks, how about the something similar for the desired result?
(I'm hoping this will clarify the how you want to address the problem you described in Post #3 which I'm not completely understanding).

 

[P0lar]

Oops sorry! Ideally I'd like to get to the following:

Team ; Mon ; Tue ; Wed ; Thu ; Fri ; Sat ; Sun

Janice ; 09:00 ; 09:00 ; Null ; 12:00 ; 12:00; 12:00 ; Null
Maggie ; Null ; 10:00 ; 10:00 ; etc...

Where the times are the most commonly occurring start times in the team, and the Null values reflect a day where the most common outcome is that there is no record in the data set for the individuals in that team. Alternatively a list of outcomes rather than a crosstab would work as well -

Team ; Day ; Start ; No. working employees

As I could then use the number of working employees as a way to calculate if the most common result was a day off.

 

[Jerry Sullivan]

Ok, I understand the goal now.

The alternative option you describe is very similar to the suggestion I made in Post #2.
You could do those steps and eliminate the Top 10 filter and you will have something like this....

Excel Workbook
	O	P
7	Row Labels	Count of StartTime
8	Janice
9	Mon	3
10	9:00	2
11	9:30	1
12	Tue	3
13	9:00	2
14	12:00	1
15	Wed	1
16	9:00	1
17	Thu	1
18	9:00	1
19	Fri	2
20	9:00	1
21	12:00	1
22	Sat	2
23	9:00	2
24	Sun	2
25	9:00	2
26	Maggie
27	Mon	1
28	9:00	1
Sheet

In this example, Janice has 3 working employees. You can extract that on Wednesday, the most common result was that 2 people had a day off- but it's not ideal.

I'm puzzling through how to make a SQL query that adds the Null StartTime records for any days off which will allow (Null) to be listed and ranked along with the actual start times.

Making good progress on that and hope to be able to post that tomorrow.