Previously we got to the stage where we had separated various periods into the different session types and began to create a pivot table of the data. Currently the pivot table is a bit of a mess and not in a condition where we can use the information. Now we will format the pivot table so it shows data in the correct format for us. The goal here is to have the pivot table showing data in the same format as the table it draws from but with data for the whole sessions showing instead of individual periods.
Before looking at how we want the pivot table data formatted, lets set the pivot table up better first:
- Select any cell within the Pivot Table
- Select Design under the PivotTable Tools Tab at the top
- Under Subtotals: Do Not Shows Subtotals
- Under Grand Totals: Off For Rows and Columns
- Under Report Layout: Show in Tabular Format & Repeat All Item Labels.
NOTE: The above does not involve changing position of any data within the pivot table (i.e. rows, columns, filters, values)
Now it’s starting to look better but the format of the various columns is off. By default pivot tables will add data and try to guess what way you want it to look however this isn’t always right, not to worry changing it is a simple task. For the types of information or metrics we look at with GPS, we will generally use Max Values (Max Velocity, Max Deceleration/Acceleration etc. ) or Sum of Values(Total Times, Total Distance etc). For Dates, we can select anything other than count.
To change the data format is nice and quick:
- Within the pivot table, double click on the column header you wish to change
- Select the correct term (Sum, Max, Average, Min….)
- Change the column name if you wish (can be useful to make it shorter)
- Change the number format if you wish (only needed if you are using this pivot table to analyse the data, for us we will be moving it on so not necessary)
- Select OK
- Repeat for desired column
Now all you need to do is select the relevant sessions under the session column and you have data for full sessions, split by session after beginning with a single export! Feels good right?? How many of you doubted me in the beginning that is wasn’t as difficult as it sounded? The sheet with your query and pivot table is now set up to be used on a daily basis, you just need to keep the filename and location of the export the same every time, then simply refresh the table the query produces!
Finally we are in a position where we can copy and paste both the individual periods and main session data into our raw data storage file. The approach outlined can be used for any type of data you wish to store whether its GPS, HR, VBT, etc. Anything that involves using a data export can be followed set-by-step. It is involves data you have manually input into Excel, then it depends on the data, maybe you need a Query or maybe you can pull it through using PowerPivot. Which is our next step…PowerPivot!
SportSciData 