Last Modified Date: 03 Jan 2019
AnswerThe below instructions are demonstrated in the packaged workbook downloadable from the right-hand Attachments section of this article.
- Drag [Customer Name] to the Rows shelf
- Right-click and drag [Order Date] to the Rows shelf
- In the Drop Field dialog, select MDY(Order Date) and click OK
- Select Analysis > Create Calculated Field
- 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]))
- Drag [Days Since Previous Date] to Text on the Marks card
- Right-click [Days Since Previous Date] on the Marks card, and select Edit Table Calculation
- In the Table Calculation dialog, do the following:
- Select Specific Dimensions
- Check all dimensions
- For Restarting every, select Customer Name
Additional InformationThe 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.
Discuss this article...
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Training and Tutorials