Pivot tables - Understanding and using them

What is a pivot table?

A pivot table is a statistics tool that summarises and reorganises selected columns and rows of data in a spreadsheet or database table (in our case, from the Odyssey database) to obtain a desired report. The tool does not actually change the spreadsheet or database itself, it simply “pivots” or turns the data to view it from different perspectives.  A pivot table allows you to see patterns, trends, and variations in your data set.

In Odyssey, Pivot tables are an extension of the search process, so you will find many places (listed below) where you can access pivot tables  via the searching process.  There are also some prebuilt (but still customisable) pivot tables in other locations such as the Depot Diary level, and in the Finance reporting area.

Learn about Odyssey's pivot tables by viewing our webinar

To watch our Odyssey community webinar on Pivot tables, click Here

Where are Odyssey's pivot tables available?

Bookings

Main Menu | Bookings | Ad hoc search data - bookings

Demographic data with sales values, not full financials.  Learn more Here

Main Menu | Bookings | Ad hoc search data - departures

Demographic data with sales values, not full financials.  COMING SOON

Depot

Main Menu | Depot | Diary | Pivot next 7 days - run info

One week's worth of depot logistics by trail and run.  Learn more Here

Main Menu | Depot | Diary | Pivot 14 days - Finishing Locations

A fortnight's worth of bike/model/size detail by "finishing location".  Learn more Here

Main Menu | Depot | Diary | Pivot table - 14 Days - Bike set up

A fortnight's worth of daily bike set up numbers, for rostering and scheduling purposes. Learn more Here

Resources

Main Menu | Resources | Bike - Ad hoc search

Allocation data; size, model, date etc.  Learn more Here

Finance

Main Menu | Finance | Reports | Departure balances by month

Summary sales data for each Departure.  Learn more Here

Category Month Value Method (e.g. Agent).

Search General Ledger code values, by date.    Learn more Here

Main Menu | Departure | Finance | Finance Details

Find Pricing elements per Departure.  Departure | Details | Finance Details | Pivot table.  Learn more Here

Main Menu | Departure | Finance | Search | Ad hoc search data - Departures

Find values of confirmed Departure.  Perform a search for date first paid initially, and released = 1. Learn more Here

How do I create a pivot table?

To create a pivot table, you will have already performed a search and then clicked the pivot table button, or used one of the prebuilt Pivot table buttons, such as one of the Depot 7 day view Pivot buttons.  In the examples below, we used a Finance | Departure Balances search:

Understanding the basics

• You have just created a search in Odyssey, and have found a whole array of data based on your search criteria, and then clicked on the Pivot table button (image above)

Pivot table view,  showing the previous pivot tables you have stored on the left hand

• You can use any existing stored pivot tables, listed on the left hand side, or create your own pivot table
• To create a pivot table, which will enable you to summarise and reorganise this data in a way which is meaningful to you, click on the + New button (this will take a few seconds)
• Give your pivot table a name, and click save  (This is super important, as even resizing the window can cause you to lose your table)
• The first pale blue column lists all of the fields which were exported in your Odyssey search.  Each search type (Trip, Booking, Client, Resource - Bike or Vehicle, or Finance, etc, all introduce different fields into this list.  The search you make determines what fields are available here.
• Drag the fields you require into the second blue column.  These fields form the Y axis (vertical axis) of your pivot table - These fields form your rows of data
• Drag the fields which will form the X axis (horizontal axis) of your pivot table into the top row of the pivot table being formed on the right hand side.  These fields form your columns of data

Your pivot table might look something like this:

• Remember to save the pivot table as you develop it, by clicking save

Refining the data selections - filters

You don't need to be constrained by the selections you made when you started your search, each of the fields in the export can be filtered, to give you more specific results:

Selecting a field from either of the axis, or even from the list of fields available from the export, opens up a box where you can make selections which of the records are displayed.  In the example below, we have selected the Trail Code, and asked that only the A2O trail is displayed in the Pivot table.  Note that the Trail Code field name is now italicised to indicate that a selection has been made

Use strong filters to give accurate results; for example when looking at Bag movements, you may want to ensure that you have excluded (unticked) Un-Released Departures, and also

• Cancelled Departures
• Cancelled Bookings
• Bag Storage

Measures

Choose your measure carefully:

e.g. Sum or Integer Sum vs Count

Use Sum or Integer Sum to add up the results of movements, for example, eg: if TWO bags are being moved at once for a client... 'Count' is only counting the record lines... not ADDING up the results - Sum or Integer Sum is what you need!

Presenting the data

Using the Pivot tables' different functions and chart types, the same data can be presented in a range of different ways

Functions

A range of mathematical functions (such as sum, average, median, sum as fraction of total), can be applied, to specified fields.  In our example, we have chosen to present the data as an average value, on the Net Value field, rather than a count, as was originally specified

Chart type

Select one of the various chart types from the drop down list to present your data in a different graphical way

or

Using the pivot table in Excel

Once you have created your pivot table, to get this data into excel, here's a super easy way to do so!

• change the first dropdown selection from Table to TSV
• Then select the text in the main box & copy

• Then open a new blank Excel spreadsheet and paste 

Measures

Choose your measure carefully:

e.g. Sum or Integer Sum vs Count

Use Sum or Integer Sum to add up the results of movements, for example, eg: if TWO bags are being moved at once for a client... 'Count' is only counting the record lines... not ADDING up the results - Sum or Integer Sum is what you need!

Sharing or duplicating a pivot table

Once you have saved a pivot table, it can be either duplicated, so that you can make a modified version of it for your own use, or shared with another user.  When the other user receives it, they can modify it.   However, your version of the pivot table is not modified, only their own version.

• Click the Share / Duplicate button

Select either Duplicate for your use

or Select a colleague's username from the Drop down list, to share this pivot table with them

click continue

• If you chose "duplicate", a duplicate of the pivot table will appear in your list of saved pivots

• In the case of sharing the pivot table, you will receive a message to advise you that this Pivot table has been copied to the selected user

A word about Dates

The Date field is treated as TEXT during the Pivot Table creation process...(which regrettably is outside of our control).To work around this, we have provided you with an additional field, to ADD to your Pivot Table layout, and have it sort by this fieldfirst.

The new field is called [DateNo], and what it is doing, is telling the Pivot Table what the Date would be IF it was a number - and then it can use that to sort in the correct chronological sequence.

If you put it BEFORE your existing DATE field, it will sort your data in the correct order.