Get date difference between current row and last sale

Question & Answer
,
1

Hi,

When I have a data set like:

Date        |  Customer   |  ProductSold
2022-01-01  |   1         |  Product A
2022-01-01  |   1         |  Product B
2022-01-01  |   2         |  Product A
2022-01-03  |   1         |  Product C
2022-01-08  |   1         |  Product D
2022-01-12  |   2         |  Product A

How could I create a calculated field that will return the days between the current row and the last sale of this customer, like:

Date        |  Customer   |  ProductSold    |  LastSaleInDays
2022-01-01  |   1         |  Product A      |   NULL
2022-01-01  |   1         |  Product B      |   NULL
2022-01-01  |   2         |  Product A      |   NULL
2022-01-03  |   1         |  Product C      |   2
2022-01-08  |   1         |  Product D      |   5
2022-01-12  |   2         |  Product A      |   11

I tried to do something with dateDiff, lag and some other functions but to me I would always need something like dateDiff(date, dateFromOtherRecord, ‘DD’) and I have no clue how you would be able to get the dateFromOtherRecord to get the actual diff

Thanks in advance.

2

Hi Jeroen- I’ve done something similar using the first date of sale with a customer. I used

First Time Customer Purchased = minOver({Order Date}, [Customer], PRE_AGG)

to get the first date a customer purchased. I think you can use the maxOver function to get the last sale of the customer. You would then use that maxOver calculated field in the dateDiff calculation.