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.

This example uses the Min, Max, and DateDiff functions. Max and Min are both aggregate functions, which means that the results depend on the grouping of the table. For more information see Aggregate functions overview. 

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. Alternatively, use the column's menu and select Group column, or press Shift-G.
  3. Group by [Date], using the same process as step 2. Make sure [Date] is in a separate grouping. Sigma automatically groups [Date] by day 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]. Select the [Customer Name] column and either use its dropdown menu or the keyboard shortcut Shift-+. 
    • Because you create the new column next to [Customer Name], Sigma creates it as a calculation (a field named [Calc]) in the same grouping.
    • Use the Min function to aggregate the lowest value of a column within a grouping. 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 column's pulldown menu or by using Shift-R in the GROUPINGS sidebar, select Rename column and change it to [First Order]. 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]. Select the [Customer Name] column and either use its dropdown menu or the keyboard shortcut Shift-+. 
    • 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]. Select the [Customer Name] column and either use its dropdown menu or the keyboard shortcut Shift-+. 
    • 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 or by pressing Shift-R, select Rename column and change the name 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: 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. For more information see Groups and groupings.

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.

This example uses the Min, Nth, and DateDiff functions. Min is an aggregate function and Nth is a window function, which means that the results depend on the grouping of the table's columns and, for Nth, also the sort ordering. For more information see Aggregate functions overview and Window functions overview.

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. Alternatively, use the column's menu and select Group column, or press Shift-G.
  3. Group by [Date], using the same process as step 2. 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]. Select the [Customer Name] column and either use its dropdown menu or the keyboard shortcut Shift-+. 
    • 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]. Select the [Customer Name] column and either use its dropdown menu or the keyboard shortcut Shift-+. 
    • 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, or press Shift-R, and change the name 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. For more information see Groups and groupings. 

Was this page helpful?
Yes No