Calculate Time Between Orders

 

Contents

Days Between the First and Most Recent Order

Days Between the First and a Second Order

 

Days Between the First and Most Recent (Latest) Order

This example shows how to find the time in days between two sales orders. It uses the PLUGS example data, specifically the [Customer Name] and the [Date] of the orders. Here is what the table looks like when the tutorial is done:
time-between-final.png

  1. Move the [Customer Name] and [Date] columns to the left of the table, where it is easier to see and work with them.
  2. Group by [Customer Name]. In the left panel, drag the column from the list into the GROUPINGS section.
  3. Group by [Date], using the same process as step 1. Make sure [Date] is in a separate grouping. Sigma automatically groups [Date] and creates a new column [Day of Date].2023-05-08_17-59-30.png
  4. Calculate the date of the first invoice. 
    • Add a new column next to [Customer Name].
    • Use the Min function to aggregate the lowest value of a column. The lowest value date is the earliest date in a list. Add the following formula to the new column: 
      Min([Date])
      Sigma automatically titles the new column according to the function, [Min of Date]. 
    • From the new columns pulldown menu, select Rename column and change it to [First Order].
      Because you created the new column next to [Customer Name], Sigma made it as a calculation in the same grouping. Your table now looks like this:
      time-between-firstorder.png
  5. Calculate the date of the latest invoice. 
    • Add a new column next to [Customer Name].
    • Use the Max function to aggregate the highest value of a column. The highest value date is the date furthest forward in time. In a list of dates that are all in the past, Max returns the most recent date. Add the following formula to the new column: 
      Max([Date])
      Sigma automatically titles the new column according to the function, [Max of Date]. 
    • From the new columns pulldown menu, select Rename column and change it to [Latest Order].
      Because you created the new column next to [Customer Name], Sigma made it as a calculation in the same grouping. Your table now looks like this:
      time-between-latestorder.png

  6. Find the number of days between orders. 
    • Create a new column next to [Customer Name].
    • Using the DateDiff function, you can find the difference between two dates. Enter the formula:
      DateDiff(“day”, [First Order], [Latest Order])
      The formula indicates that the results are reported in days, that [First Order] is the first date and [Latest Order] is the latest date. The result is the number of days between the first order and the latest order.
    • From the new column's pulldown menu, select Rename column and change it to [Days First to Latest Order].
      Your table now shows the time between first and latest orders, with all calculations in the Customer Name group:
      time-between-daysbetween.png

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.

Days Between the First and a Second Order

This example shows how to find the time in days between two sales orders. It uses the PLUGS example data, specifically the [Customer Name] and the [Date] of the orders. Here is what the table looks like when the tutorial is done:

timebetweenfirstandsecond-final.png

 

  1. Move the [Customer Name] and [Date] columns to the left of the table, where it is easier to see and work with them.
  2. Group by [Customer Name]. In the left panel, drag the column from the list into the GROUPINGS section.
  3. Group by [Date], using the same process as step 1. Make sure [Date] is in a separate grouping. Sigma automatically groups [Date] and creates a new column [Day of Date].
  4. Use the [Day of Date] column's menu to sort it in ascending order, so that the first order date is on top and the latest on the bottom. The table looks like this:timebetweenfirstandsecond-groupandsort.png
  5. Calculate the date of the first order. 
    • Add a new column next to [Customer Name].
    • Use the Min function to aggregate the lowest value of a column. The lowest value date is the earliest date in a list. Add the following formula to the new column: 
      Min([Date])
      Sigma automatically titles the new column according to the function, [Min of Date]. 
    • From the new columns pulldown menu, select Rename column and change it to [First Order].
      Because you created the new column next to [Customer Name], Sigma made it as a calculation in the same grouping. Your table now looks like this:
      timebetweenfirstandsecond-firstorder.png

  6. Calculate the date of the second order. 
    • Add a new column next to [Customer Name].
    • Use the Nth window function that returns the value of the Nth row of a group or column. The table is grouped by the invoice date, and is sorted ascending. Therefore, the date of the second order will always be in the second row. (Note: This tutorial assumes that there is only one order per day.) Add the following formula to the new column: 
      Nth([Day of Date], 2)
      Sigma return the second row of [Day of Date] for all of the groups defined by [Customer ID]. Because [Day of Date] is truncated to the day, unlike [First Order] the new column does not show the hours and minutes. 
    • From the new column's pulldown menu, select Rename column and change it to [Second Order]. Your table now looks like this:
      timebetweenfirstandsecond-secondorder.png
  7. Find the number of days between the first and second order. 
    • Create a new column next to [Customer Name].
    • Using the DateDiff function, you can find the difference between two dates. Enter the formula:
      DateDiff(“day”, [First Order], [Second Order])
      The formula indicates that the results are reported in days, that [First Order] is the first date and [Second Order] is the second date. The result is the number of days between the first order and the latest order.
    • From the new column's pulldown menu, select Rename column and change it to [Days First to Second Order].
      Your table now shows the time between first and latest orders, with all calculations in the Customer Name group:
      timebetweenfirstandsecond-between.png
    • Collapse the Customer Name column to see more customer rows. 

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.

Was this page helpful?
Yes No