Calculate Time Between Sales
We’re going to look at how to find the time between two orders. We’re starting with a List of Customer IDs, and a list of Invoice Dates. We’ll end with the data on the Workbook below.
Create a new Workbook and add a table element with your order information.
Time Between First and Most Recent Order
- Group by [Customer ID]
- Group by [Invoice Date]
- Calculate the date of the first invoice. The Min function is an aggregate that returns the lowest value of a column. The lowest value date is the earliest date in a list. To find the Min, add a new column next to [Customer ID] and use the formula Min([Invoice Date]). Rename the new column ‘First Order’.
- Calculate the date of the latest invoice. The Max function is an aggregate that returns the highest value of a column. The highest value date is the date furthest forward in time. In a list of invoices, dates that are all in the past, Max returns the most recent date. To find the Max, add a new column next to [Customer ID] and use the formula Max([Invoice Date]) Rename the new column ‘Latest Order’.
- Find the number of days between orders. Using the function DateDiff, we can find the difference between two dates. Create a new column next to [Customer ID] and enter the formula DateDiff(“day”, [First Order], [Latest Order]). This indicates that we want the results to be reported in days, that [First Order] is the first date and [Latest Order] is the second date, and that we want to know the difference between them. The result is the number of days between the first order and the latest order.
TIP: Max and Min are both aggregate functions, which means that the results depend on the grouping of the table. To see the data in different groupings, you can create a child element in your workbook. The parent table will be used as a data source for the child element, and you can use different groupings without affecting the calculations in the parent table.
Time Between First and Second Order
- Group by [Customer ID]
- Group by [Invoice Date]
- Sort [Invoice Date] Ascending, so that the first invoice date is on top and the latest on the bottom.
- Calculate the date of the first invoice. The Min function is an aggregate that returns the lowest value of a column. The lowest value date is going to be the earliest date in a list. To find the Min, add a new column next to [Customer ID] and type Min([Invoice Date]). Rename the new column ‘First Order’.
- Calculate the date of the second invoice. To find the date of the second invoice, we’ll use Nth which is a window function that returns the value of the Nth row of a group or column. We have our table grouped by the invoice date, which is sorted ascending. Therefore, we know that the date of the second order will always be in the second row. Create a new column next to [Customer ID] and enter the formula Nth([Invoice Date], 2). This will return the second row of [Invoice Date] for all of the groups defined by [Customer ID].
- Find the number of days between orders. Using the function DateDiff, we can find the difference between two dates. Create a new column next to [Customer ID] and enter the formula DateDiff(“day”, [First Order], [Second Order]). This indicates that we want the results to be reported in days, that [First Order] is the first date and [Second Order] is the second date, and that we want to know the difference between them. The result is the number of days between the first and second order.
TIP: The results of the functions Min and Nth depend on the grouping and sorting of the table. To see the data in different groupings, you can create a child element. The parent table will be used as a data source for the child element, and you can use different groupings without affecting the calculations in the parent table.