Pivot Table example 3: Frequency distribution

OpenOffice.org 3 Calc Guide

Chapter 8 Using the DataPilot

< Previous Section

Next Section >

• Example: Sales volume overview
• Example: Timekeeping
• Example: Frequency distribution
• DataPilot functions
• Data source
• DataPilot dialog
• Working with the results of the DataPilot
• Sorting, drilling, filtering, refreshing
• Multiple data fields
• GETPIVOTDATA

< Previous Page

Next Page >

For showing the frequency of incidents, Calc uses the function FREQUENCY. This formula has to be used in a so-called matrix formula. This is an advanced feature. Alternatively you can use the DataPilot, which requires almost no knowledge of spreadsheets.

In our example, we want to investigate the number of emails that go to the Germanophone support mailing list (users@de.openoffice.org). We want to know how the activity on the list is distributed during the day.

The starting point for this example is the mailbox file of the Thunderbird mail client. The command

grep 'Delivery-Date:' Inbox

creates a text file that contains for the timespan of more than 2 years one line for each message, with date and time.

Raw data for the frequency of the messages

To import the data into a table in Calc, open the text file users.txt with the raw data. The Text Import dialog appears; here you can choose your import options. Use the following properties:

Separator options: Fixed width

Divide into columns at position 20, 31 and 40

Column types:

Column 1: Hide
Column 2: Date (DMY)
Column 3: Standard
Column 4: Hide

Import settings

The figure below shows the imported raw data with a line added at the top containing titles for each column.

Raw data in Calc

Solution with a matrix formula

To calculate the frequency, you have to create 24 classes, one for each hour. In the next column, you enter the number of emails with the function FREQUENCY.

FREQUENCY function in a matrix formula

The first argument is the cell area with the times of all approximately 38,000 emails. The second argument is the cell area E2:E25 that describes the frequency classes. To enter the formula, first select the cell area F2:F25, then enter the formula. Then finish the formula by using the key combination  ⇧ Shift  +  Ctrl  +  ↵ Enter . This indicates to the program that you want to use a matrix formula. To indicate the matrix formula, the program uses curly brackets.

This technique is most often known only to advanced users.

Solution with the Pivot Table

With the Pivot Table you can achieve the same result much easier and faster. The solution is also possible for less advanced users. Starting with the raw data, you need only a few mouse clicks:

1. Select the cell A1 (or any other cell within the list).
2. Choose Data → Pivot Table → Create and click  OK .
3. In the Pivot Table:
• Drag time into the Row fields area.
• Drag date into the Data fields area.
4. Click  More  to show more options in the lower part of the dialog.
5. Choose – new sheet – for Results to.

Pivot Table dialog
6. In this case we need to count the number of values, not their sum. Double-click on the field Sum – Date to open the Data Field dialog and select the function Count.

Properties of the data field
7. Click  OK . As an intermediate result, you get a Pivot Table that has a separate line for every time within the raw data.

This may be a very time-consuming process because of the large number of items. The time does not depend that much on the number of lines, but rather on the number of rows needed for the table that contains the results.

8. For grouping the rows, select the cell A4 or any other cell that contains a time.
9. Choose Data → Group and Outline → Group, select for the interval Hours and click  OK . The result is now grouped according to hours.

properties for grouping according to hours

The next figure shows the Data Field dialog for the data field Number – Date. Click  More  and select as type % of column.

Data Field settings for relative values

The result is shown below, with absolute occurrence on the left.

Frequency distribution with the Pivot Table

Relative occurrence

Whether the relative values are shown as a decimal (0.1) or as a percentage (10%) depends only on the cell formatting itself and has nothing to do with the Pivot Table.