Magento Business Intelligence Help Center

Using the Date Difference Calculated Column

 This document outlines the purpose and uses of the "Date Difference" 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. Please note: This is currently a beta feature.

 Explanation 

The "Date Difference" column type finds the time between two events belonging to a single record, based on the event timestamps. The raw value calculated in this column is in seconds, but it will auto-convert to minutes, hours, days and so on for display on reports. When used as a filter/group by, however, you will want to use the value in seconds. 

A date difference calculated column could be used to create a metric which calculates the average or median time between two events, such as average time between customer registration and their first orders.

 Example 

id 

timestamp_1 

timestamp_2 

Seconds between timestamp_2 and timestamp_1 

A 

2015-01-01 00:00:00 

2015-01-01 12:30:00 

45000 

B 

2015-01-01 08:00:00 

2015-01-01 10:00:00 

7200  

 In the above example, the "Date Difference" column is the "Seconds between timestamp_2 and timestamp_1" column. It performs the calculation "timestamp_2 minus timestamp_1". 

 Mechanics 

Here are some instructions on creating a "Date Difference" 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 a Column" and choose the "DATE_DIFF (…)" column type under the "Same Table" section. 
  4. Choose the two timestamps that you would like to find the time between. 
    1. The first box is the later timestamp i.e. the timestamp that will (usually) occur later. 
    2. The second box is the earlier timestamp i.e. the timestamp that will (usually) occur earlier. 
  5. Provide a name to the column and hit "Save". 
  6. The column will be available to use immediately. 

date_diff.gif

For any questions, please feel free to reach out to the support team at support@rjmetrics.com. 

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk