# Sequential Comparison Calculated Column

Last update: 2023-07-24
• Created for:
• Beginner
Intermediate
Developer
User

This topic outlines the purpose and uses of the `Sequential Comparison` calculated column available in the Manage Data > Data Warehouse page. Below is an explanation of what it does, followed by an example and the mechanics of creating it.

Explanation

The `Sequential Comparison` column type: finds the difference between consecutive events. The most common type of `Sequential Comparison` column is the `Seconds since previous order` column. There are three inputs needed for this column:

1. `Event Owner`: This input determines the entity for which rows are grouped. For example, in the `Seconds since previous order` column, the event owner is the customer, because you want to find the number of seconds since the previous order of the same customer.
2. `Event Date`: This input enforces the sequence of events. In the cases of `Seconds since previous order`, the column containing the timestamp of the order should be the `Event Date`. This input is always a timestamp.
3. `Value to Compare`: This input is the actual value to be compared. It subtracts the previous row’s value from the current row’s value. Hence, a column finding the time difference between successive orders of a customer is called `Seconds since previous order`. This input does not have to be a timestamp. A non-timestamp example is to find the difference in order value between successive orders of a customer.

Example

`event_id` `owner_id` `timestamp` `Seconds since owner's previous event`
`1` A 2015-01-01 00:00:00 NULL
`2` B 2015-01-01 00:30:00 NULL
`3` A 2015-01-01 02:00:00 7200
`4` A 2015-01-02 13:00:00 126000
`5` B 2015-01-03 13:00:00 217800

In the above example, `Seconds since owner's previous event` is the `Sequential Comparison` calculated column. For the `owner_id = A`, it first identifies a sequence based on the `timestamp` column, and then subtracts the previous event’s `timestamp` from the current event’s timestamp. In the third row in the table – the second row for `owner_id A` – the value of `Seconds since owner's previous event` is the number of seconds between ‘2015-01-01 02:00’ and ‘2015-01-01 00:00:00’. This difference equals two hours = 7200 seconds.

For this calculated column type, the row corresponding to the owner’s first event has a `NULL` value.

Mechanics

To create an Event Number column:

1. Navigate to the Manage Data > Data Warehouse page.

2. Navigate to the table on which you want to create this column.

3. Click Create New Column in the upper-right corner.

4. Select `Same Table` as the `Definition Type` (if the columns that you want to compare are not on the same table you may need to relocate them).

5. Select `SEQUENTIAL_COMPARISON` as the `Column Definition Equation`.

6. Choose the inputs, as explained above:

• `Event Owner`
• `Event Date`
• `Value to Compare`
7. Filters can also be added to exclude rows from being considered. The excluded rows have a `NULL` value for this column.

8. Provide a name for the column at the top of the page and click Save.

9. The column is available to use immediately.