Loading

Calculate Difference in Sequential Dates in One Date Field

Дата публикации: Feb 27, 2025
Задача
How to calculate the difference in days between a date and the previous date in the same date field. For example, to see how many days are between each order a customer has placed.
Действия
The below instructions are demonstrated in the packaged workbook downloadable from the right-hand Attachments section of this article.
  1. Drag [Customer Name] to the Rows shelf
  2. Right-click and drag [Order Date] to the Rows shelf
  3. In the Drop Field dialog, select MDY(Order Date) and click OK
  4. Select Analysis > Create Calculated Field
  5. In the Calculated Field dialog box that opens, do the following, and then click OK:
    • Name the calculated field. In this example, the calculated field is named "Days Since Previous Date"
    • In the formula field, create a calculation similar to the following:
    DATEDIFF('day', LOOKUP(MIN([Order Date]),-1), MIN([Order Date]))
  6. Drag [Days Since Previous Date] to Text on the Marks card
  7. Right-click [Days Since Previous Date] on the Marks card, and select Edit Table Calculation
  8. In the Table Calculation dialog, do the following:
    • Select Specific Dimensions
    • Check all dimensions
    • For Restarting every, select Customer Name
Дополнительные ресурсы

The DATEDIFF() function will find the number of specified dateparts, in this case days, between two dates. The starting date is LOOKUP(MIN([Order Date]),-1), which will return the date one row previous.

Table functions can be computed differently to allow the calculation to be further customized. In step 8, this example we restart the count for every customer. For more information on table functions can be changed, please see Transform Values with Table Calculations.

Номер статьи базы знаний

001458030

Вложения

days since previous date.twbx

1206 KB

 
Загрузка
Salesforce Help | Article