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. As an alternative you can use the DataPilot, which requires almost no knowledge about spreadsheets.

In our example we want to investigate the number of emails that go to the Germanophone support mailing list ( [email protected]). 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 with 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 to advanced users only.

Solution with the DataPilot

With the DataPilot you can achieve the same result much simpler and faster. The solution is also feasible for the less advanced user. 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 the field time into the layout area Row fields.
• Drag the field date into the layout area Data fields.
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 (summing dates is rather silly). 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.

The time needed for creating the table might be huge because of the large number of items. Please be patient. The time does not depend that much on the number of lines but 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 figure below shows the absolute occurrence.

Frequency distribution with the DataPilot

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.

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.