4 Replies Latest reply on Jan 2, 2019 6:37 AM by Jason Kim

    Plotting customer orders by recency (X days ago since last order) with date range filter

    Jason Kim

      Hello!

       

      I am trying to make a bar graph that plots the # of orders by the amount of days that have elapsed since the last order a user made and the last date set in the Date Range Filter. This "days ago" calculation is further grouped into buckets of time (see below).

       

      The process is very similar to this Tableau article, except I don't want to use a parameter, but a Date Range Filter which the user can dynamically set the start and end date.

       

      I couldn't find any documentation of whether there is a variable for the end date in the Date Range filter similar to how today() functions -- this would be perfect to use for my purposes.

       

      Here is what I've come up with so far for the Recency calculated field:

      IF DATEDIFF('day',Max([Order Date]),today()) <= 1 THEN '1 or Less Days Ago'

      ELSEIF DATEDIFF('day',Max([Order Date]),today()) > 1 AND DATEDIFF('day',Max([Order Date]),today()) < 5 THEN '2 - 4 Days Ago'

      ELSEIF DATEDIFF('day',Max([Order Date]),today()) > 5 AND DATEDIFF('day',Max([Order Date]),today()) < 8 THEN '5 - 7 Days Ago'

      ELSE '>7 Days Ago'

      END

       

      I'd like instead of the end time of today() to be the end date selected in the Date Range filter. For the start time, I'd like it to be the last order a user placed in the date range selected -- I think MAX(Order Date) may not be doing this.

       

      The end result will have 4 bars coinciding with the "days ago" buckets above, with # of users who ordered in the date range selected being on Y axis.

       

      Thanks in advance for any help or pointers you can throw my way. I've attached a workbook that uses the superstore data as the example.

       

      Let me know if you need any clarifications,

       

      Jason