The Data Difference transform is used to calculate time between two different date columns. This transform allows you to get results back in plenty of different units including days, weeks, months, quarters and years. You'll find the Date Difference transform under the Clean & Prepare section of the Transform Menu.
To better understand this transform, let's take a look at an example. A sales manager wants to find out how long (on average) her employees are taking to close a deal. Say she has the date the deal was created (Created Date) and the date the deal was closed (Close Date) but, doesn't actually have the data on how long it took the sales rep to close the deal. This sales manager can use the Date Difference transform to find the difference between the Created Date and Close Date.
For another example, take a shipping company who wants to know the average processing time from the time the products are order to when they were shipped. In this case they might want the elapsed time between columns like Order Date to Shipping Date. They would use the Date Difference transform to calculate the time between the Order Date and Shipping Date.
Using the Date Difference Transform
To demonstrate how to use this transform, let's walk through the example above to calculate the time to close a sale.
-
Click the +Add Transform button and select the Date Difference transform from the Clean & Prepare section.
Name the new column you will be making by filling in the New column namefield.
Choose the start date for this transform by filling out the Calculate time fromfield.
-
Choose the end date by filling out the To field.
Fixed date: Both the Calculate time from and the To fields have the option for a fixed date selection. This generates a new filed for you to choose a fixed date to use in the calculation. Note that this could cause negative dates if the the Calculate time from ever ends up being further in the future than the fixed date chosen.
Dynamic date: As with the fixed date, both Calculated time from and the To fields have the option for a dynamic date selection. This allows you to find date differences to or from dynamic dates like Yesterday, Today, and the Start and End of week, month, quarter, year, and fiscal year (if set in the account).
-
Choose the units your would like your result column to display in the New column units field. You can choose between the following units: day, week, month, quarter, year or interval.
Interval: The interval selection will show the value in the number of years, months, and days. For example, say the result of the data difference transform was 60 days. Selecting Interval would provide the following output 1 month 29 days. This is assuming the first month included 31 days.
-
Choose what kind of rounding, if any, you would like to apply to your new calculated column in the Rounding field. You can choose from the following: No rounding, Normal rounding, Round down, and Round up.
- No Rounding: 4.75 days becomes 4.75 days
- Normal Rounding: 4.75 days becomes 5 days
- Round down: 4.75 days becomes 4 days
- Round up: 4.75 days becomes 5 days
Choose whether or not you want to Include Labels. To include labels shows the units you've selected in the New column units dropdown. For example, say you've selected days and the outcome of the date difference transform is 4.75 days. If you check the Include Labels box, the answer would come out as 4.75 days. However, if you uncheck the Include Labels box, the answer would just be 4.75.
