DataPilot 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 DataPilot

With the DataPilot 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 > DataPilot > Start and click OK.
3. In the DataPilot:
• 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.


DataPilot 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 DataPilot table that has for every time within the raw data a separate line.

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 DataPilot

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 DataPilot.