Calculate Time Between Sales

Posted on
November 6, 2018

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 Worksheet below.

Time Between First and Most Recent Order

  1. Group by [Customer ID]
  2. Group by [Invoice Date]
  3. 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, open the column menu and select ‘Aggregate Column; then ‘Min’. Rename the new column ‘Date of First Order’.
  4. 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, open the column menu and select ‘Aggregate Column’ then ‘Max’. Rename the new column ‘Date of Latest Order’.
  5. Find the number of days between orders. Using the function DateDiff, we can find the difference between two dates. Create a new column and enter in the formula DateDiff("day", [Date of First Order], [Date of Latest Order]). This indicates that we want the results to be reported in days, that [Date of of First Order] is the first date and [Date of 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.

Max and Min are both aggregate functions, which means that the results depend on the grouping of the worksheet. To see the data in different groupings, you can build a new Worksheet on top of the current Worksheet. The new Worksheet will treat the current Worksheet as a static data source, and you’ll be able to group the new Worksheet whichever way you want.

Time Between First and Second Order

  1. Group by [Customer ID]
  2. Group by [Invoice Date]
  3. Sort [Invoice Date] Ascending, so that the first invoice date is on top and the latest on bottom. Open the column menu on [Invoice Date] and choose ‘Sort Ascending’.
  4. 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. Open the column menu and select ‘Aggregate Column; then ‘Min’. Rename the new column ‘Date of First Order’.
  5. 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 worksheet 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 and enter in the formula Nth([Invoice Date], 2) . This will return the second row of all of the groups in the column [Invoice Date].
  6. Find the number of days between orders. Using the function DateDiff, we can find the difference between two dates. Create a new column and enter in the formula DateDiff("day", [Date of First Order], [Date of Second Order]). This indicates that we want the results to be reported in days, that [Date of of First Order] is the first date and [Date of 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.

The results of the functions Min and Nth depend on the grouping and sorting of the worksheet. To see the data in different groupings, you can build a new Worksheet on top of the current Worksheet. The new Worksheet will treat the current Worksheet as a static data source, and you’ll be able to group the new Worksheet whichever way you want.

Posted on
November 6, 2018
in
Tips & Tricks
category

Ready to Speak Sigma?