datedifftableau-desktop

Deriving a calculation for re-orders by date and customer name


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

Solution

  • Method used is Dense_Rank function and PARTITION with Lookup:

    Dense_Rank:

    ^ Column labeled as Order_Rank_Customer

    Partition/Lookup:

    Days Since Last Order: