Pivot Data and Create Statistics

Excel Pivot Table 

A Pivot Table is used to summarize, sort, reorganize, group, count, total or average data stored in a table. It allows us to transform columns into rows and rows into columns. It allows grouping by any field (column), and using advanced calculations on them.  

In short, Pivot can: 

• group items/rows into categories 

• count the number of items in each category,  

• sum the value of the item 

• or compute an average, find minimal or maximal value, etc. 

Default COMET Pivot

Some COMET users avoid using the pivoting options because it seems complex. If you’re one of them, you’ll be thrilled to see the new COMET Pivot feature that lets you set the default layout for your pivot tables.

Default COMET Pivot feature is available for these reports:

• Persons

  • Person Registrations

  • Person Contracts

• Organizations

  • Organizations  

• Events

  • Event Participants

• Competitions

  • Competitions

  • Competition Person Registrations

  • Matches

  • Player Appearances

  • Team Official Appearances

  • Match Official Appearances

  • Match Events

• Licensing

  • Applications

  • Application Categories

  • Application Evidence

• Disciplinary

  • Disciplinary Sanctions

• Users

  • User roles

• Step 1: Export the Organizations report, open it in Excel and open the Pivot tab

• Step 2: Use the information from the default Pivot

• Step 3: Of course, if you need a more advanced report, simply drag & drop PivotTable fields from or to any area (filters, columns, rows, or values)

Advanced COMET Pivot Example 

For example, you want to know the number of confirmed players registered in your Association by nationality. 

• Step 1: Export the Person registrations report and open it in Excel. 

• Step 2: Click on the cell A1 and press Shift + CTRL + right arrow® + down arrow ¯  to select the whole table. Then switch to the Insert tab and select the Pivot option in the top left corner 

• Step 3: Click on the Ok button. New tab for Pivot analysis will be opened automatically  

• Step 4: Drag and drop Fields (Nationality, Org name) to the Columns (Nationality) and Rows (Org name). You’ll notice that the Clubs are now listed in the table under the Rows, and Nationalities under the Columns. 

• Step 5: Drag and drop Field (Player ID) to the Values. Since the Sum of Person IDs doesn’t give us the information we were looking for, next step is changing the Value Field Settings to Count by pressing the little arrow button next to the Sum of Person ID. 

• Step 6: If you prefer charts, switch to the tab Analyze and select Pivot chart. Pivot chart will automatically suggest the chart based on your data. 

Please check video tutorial: https://youtu.be/CxcGQmTdERc   

Use of parameter Add this data to the Data Model

… to see the answers to survey questions in the Order Item Survey Answers.

The "Add this data to the Data Model" option takes your data analysis to the next level. When selected, this parameter incorporates the dataset into Excel's Data Model, a powerful feature that allows for more complex and comprehensive analysis. The Data Model enables users to create relationships between tables, utilize advanced calculations, and build more sophisticated reports.

We will continue from the Step 3:

• Step 3: Click on the "Add this data to the Data Model" option and Ok button. New tab for Pivot analysis will be opened automatically 

• Step 4: If you want to use actual value instead of Sum, Count or whatever, you need to create a measure (right-click on Range)

• Step 5: Put following formula =FIRSTNONBLANK(columnName, ““) in this case (Order Item Survey Answers report) it is =FIRSTNONBLANK(Range[Answer];"")

• Step 6: And then add this measure as Value