24. Calculate Date Difference

Note: The scenario below uses data from the DESelect Demo Data Package.

Scenario: You have a Data Extension (Orders) that contains a column with Order Dates and you want to add a new field to your Target Data Extension that calculates the number of days between the Order Date and the current date.

Note: This scenario uses the feature Custom Values, which is available in DESelect Plus and Advanced.

1. Input

On the overview of Selections screen, click on New Selection to create a new one. 

Drag Data Extension DESELECT_DEMO_Orders to the Selected Data Extensions section. 

2. Output

Hit the Create Data Extension button, enter a name, e.g. Orders Target, and press Save.

Select the following fields by either double-clicking on them or dragging them to the section on the right:

• CustomerId, Order Date from DESELECT_DEMO_Orders.

Click Save Data Extension.

Now we're going to add a Custom Value:

• Under Custom Values on the left, click Add new value.
• Under Name, enter: Days Since Order and hit Next.
• Choose Type: Apply formula to a field.
• Choose Type: Date Difference.

A modal appears where you can do the setting.

• Add the first date:
  • Under Date 1, make sure the Field option is selected.
  • Under Data Extension select DESELECT_DEMO_Orders.
  • Under Field select Order Date.
• Add the second date:
  • Under Date 1, make sure the Timestamp option is selected.
  • Select Date.
• Select Day in the Express difference in drop-down list.
• Hit Save.

The logic defined above will, for each row, calculate the difference between the value of the Order Date column and the current date in days and return the result.

Now that the Custom Value Days Since Order has been created, we can map it to the Target Data Extension fields.

• Click the gear icon > Edit Target Data Extension Fields.
• Drag-and-dropping Days Since Order from Custom Values to the Target Data Extension fields.

3. Preview

When you click Run Preview on the Preview screen, a results table will be shown with fields

• CustomerId and Order Date from DESELECT_DEMO_Orders.
• Days Since Order, populated with the number of days between the date of the Order Date field and the current date.