Pivot Tables


What are Pivot Tables? This is certainly one of the most frequently asked questions. Pivot Tables are summary reports which can be grouped by different criteria, which allows calculation of various values based on these groups. Pivot Tables are also referred to as inverted grids. To learn more about pivot tables, please refer to the documentation for Excel or other spreadsheet programs. In this case, perhaps the best description is a picture.... Note, the image below is based on a DXCC statistic whereas, the example used in the tutorial creates a Pivot Table based on a DOK statistic.

Click here for a separate tutorial that creates a DXCC Pivot Table.



Before you create a Pivot Table you should decide what information you would like to display. A Pivot Table consists of the following fields:

Create a Pivot Table

The creation of the Pivot Table is explained by using an example based on the DOK statistic. The Pivot Table will be created in two steps: First, the table will be created showing the following entries:

The second step, will be extended in such a way that it also shows:

When you understand the entire process you will be able to create your own Pivot Table for WAE, WAZ, DOK and others.

This example assumes that you have the DOK statistic in your database. If not, you can import the statistic – from the main menu click on File ¦ Statistic Import / Export ¦ Import Statistic and select the appropriate statistic from the list, or click on Statistic Import/Export for more detailed information.

To create a Pivot Table click on Wizards ¦ Create Pivot-Table.

The dialog window Selection of the Group-Fields is displayed as shown below:

  1. Near the bottom of the window, click on the arrow at the right of the field Statistic as templateA list of the available statistics will appear, select DOK.
     
  2. In the window Group-Fields the value SUB_REGION appears. This is the field where the DOK has been stored.

  1. Press the Continue button.
     
  2. The dialog window Select Evaluation-Field is displayed next:

 

  1. Since we want an evaluation per Band, select Band in the list of Available Fields and press the > button to move the field into the Evaluation-Field list.
     
  2. Press the Continue button.
     
  3. The dialog window Select Calculation-Field is displayed next:

  1. In this step, you define which field is used for the values displayed at the cross points and how these values should be calculated. In our DOK example we would like to display the call sign of the first station worked on each of the selected bands.

  1. You can display values which are aggregated within the group of QSO's that are presented by row. The available (aggregate) functions are shown in the function list. First and last are not real aggregates – however, they allow you to display values of fields for the first or last QSO in a group.

 

The dialog window Select and display QSO's contains of two pages, the Functions page and the Pivot Table page. The functions page allows you to select the following Functions:

  1. Enter the selection criteria by checking the appropriate boxes. For example check; AM, CW, FM, SSB in the Modes column, and check 10m, 15m, 20m, 40m, and 80m in the Band column.
     
  2. Press the Continue button, this will bring you back to the first part of the Select and display QSO's dialog window.
     
  3. Now we are ready to display the results – click on the Pivot Table Tab and wait a few moments until the table is displayed.

The image above shows the results of a Pivot Table based on the DOK statistic – the data is explained as follows:

P_Sub_Region – shows the DOK.

C_Call – lists the total number of QSO's which exist for this DOK.

L_QSL_Received – lists the number of QSL Cards which have been received from the DOK.

L_Call – displays the Callsign of the first QSO in the group of QSO's with the DOK.

10m, 20m, etc... – displays the Callsign of the first QSO on the applicable band with the DOK.


Copyright © 2004 SWISSLOG
Last modified: 01 Jan 2005