I am trying to derive a calculation for re-orders by date and customer name. With the output being in "weeks". And if its easy to switch between "days" and "months" as well.
For example. " Marriot overall had xx weeks on average between orders, and Marriot Los Angeles had xx weeks on average between orders".
Also would need to be able to calculate "Last order from today". So for example if a customers last order was "Jan 1st 2023" and they never ordered again, I would like to calculate the time between than and today (current_timestamp)
Essentially a Datediff, but had trouble getting it to work while factoring in the customer name and customer name sub.
Order Date | Customer Name | Customer_Name_Sub |
---|---|---|
5/20/2022 | Marriot | Los Angeles |
5/20/2022 | Marriot | New York |
5/26/2022 | Marriot | Los Angeles |
5/27/2022 | Marriot | Miami |
6/10/2022 | Marriot | Los Angeles |
6/16/2022 | Marriot | Miami |
6/22/2022 | Marriot | Los Angeles |
6/29/2022 | Marriot | Miami |
7/6/2022 | Marriot | Los Angeles |
7/8/2022 | Marriot | Miami |
7/13/2022 | Marriot | Miami |
7/20/2022 | Marriot | Miami |
7/28/2022 | Marriot | Miami |
8/3/2022 | Marriot | Los Angeles |
8/30/2022 | Marriot | Los Angeles |
9/14/2022 | Marriot | Los Angeles |
10/18/2022 | Marriot | Los Angeles |
12/22/2022 | Marriot | Los Angeles |
1/9/2023 | Marriot | Los Angeles |
Method used is Dense_Rank function and PARTITION with Lookup:
Dense_Rank:
This gives a ranked number for each order date by the customer value to know what orders came first and last
{PARTITION [Customer Name]: { ORDERBY [Order Date] ASC: RANK_DENSE()}}
^ Column labeled as Order_Rank_Customer
Partition/Lookup:
This uses the Dense_Rank as a reference to know which dates by customer were first/last. This brings back the previous order of the specific customer to generate a column I labeled "Previous Order Date"
{ PARTITION [Customer Name]: { ORDERBY [Order_Rank_Customer] DESC: LOOKUP([Order Date],1)}}
Days Since Last Order:
This is a simple DATEDIFF with a ABS (Absolute value) since it generates a negative number
ABS(DATEDIFF('day',[Order Date],[Last Re-Order Date Customer]))