How to set up date variables to have time lookback

joebenz
joebenz Member
in Variables

I am trying to create a card that will properly look back in time so that the dates being filtered reflect that look back in time.

Here is the situation

  • We get ratings from customers and want to wait a certain amount of time to measure the 'response rate'
  • We do this by saying that it, on average, takes customers 75 days to fill out the survey once they have created a profile with us. Therefore, we'd want to take the Created Date of a customer and subtract 75 days

I'm hoping to be able to use this date variable in the dashboard level date filter (i.e. be able to select This Year). Here's an example:

  • If selecting This Year (i.e. Jan 1 to Jan 10), I'd want the cards to be filtering 75 days earlier from Jan 1 as the start date and 5 days earlier from Jan 10 as the end date

What is the best way to do this?

Answers

  • marcel_luthi
    marcel_luthi Coach

    Not sure if the End Date part was a Typo or not, and whether this 75 days is a fixed value or not. If the value is fixed you could do this at the ETL Level and have a Displaced Created Date which is calculated as DATE_SUB(Created Date, INTERVAL 75 DAY)and use this field in your card instead.

    If the displacement needs to be adjustable at the view level, then use a Variable and create a beast mode with the same logic DATE_SUB(Created Date, INTERVAL variable DAY) and use that one as your date field for the card.

    Hope this helps!

  • joebenz
    joebenz Member

    Hi Marcel, thanks for hopping in! Yes, my fault. It's static 75 days on both ends.

    I did try adding an Adjusted Date with the Date_Sub function - however, I run into the issue of the user not being able to filter the dates to match up with what they view as the timeline. I may be thinking of this in the wrong way, but here's what I'd be looking for.

    • The user would select 'This Year' (or some other date timeframe) for the dashboard level filter, and today that would include the dates 1/1/24 to 1/10/24
      • If I were to filter on the Created Date, then it would not populate any of the customers in the adjusted timeframe
      • If I were to filter on the Adjusted Date (for This Year), then no customers would show up because it's adjusting all created dates to that 75 day subtraction

    So I'd be hoping for the Date Filter to be saying the dates that the user would be filtering for, but would want the start date and end date to actually pull the customers in the adjusted window. Does that make sense?

  • marcel_luthi
    marcel_luthi Coach

    I guess I'm not fully following, so let's walk through this with an example. Let's suppose you have a table with the following 10 entries.

    Entry Id

    CreatedDate

    1

    11/5/2023

    2

    11/15/2023

    3

    11/18/2023

    4

    12/5/2023

    5

    12/11/2023

    6

    12/22/2023

    7

    1/1/2024

    8

    1/5/2024

    9

    1/6/2024

    10

    1/11/2024

    If the user selects a time Range of Year to Date (1/1/24 to 1/11/24), which rows would you expect to be shown after filtering? You might need not to use DATE_SUB but DATE_ADD instead if you want to pull what was closed in the past to more recent dates.

  • joebenz
    joebenz Member

    Hi @marcel_luthi, apologies for the delay here. Basically here's what I'd be hoping for.

    Screenshot 2024-02-08 at 2.12.46 PM.png

    The goal would be for a user to use the Dashboard level filter (i.e. This Year), and I'd want to then only use the Response Rate calculation with the rows that have the 75 day lookback of the created date.

    So, for example, if the user was pulling This Year, then the dates would be Jan 1 to Feb 8. Therefore, I'd want the resulting calculation to include customers that were created between 10/18/23 (Jan 1 minus 75 days) and 11/25/23 (Feb 8 minus 75 days).

    Since the users who would be looking for this metric might not be savvy enough to know they should be pulling the dates of 10/18/23 to 11/25/23, I'm hoping that they can select This Year and have the results have the look back embedded.

    Does that make sense?

Categories