Case Statement - 28 Days from a specific date?

Options
pauljames
pauljames Contributor
edited April 25 in Beast Mode

Hi All,

I need to write a case statement that would calculate 28 days from a selected date. How would I write that?

The end result would be total sales within the 28 days going back in time from the date selected.


Thanks,
Paul

IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

Tagged:

Best Answer

  • david_cunningham
    Answer โœ“
    Options

    I wanted to write up a more consolidated and easy to follow answer for anyone else curious about how to do this. This answer also expands on the requested functionality, by adding in a toggle that will allow users to flip between limiting the data based on a selected date, and showing all data. As well as allows users to specify the number of days to calculate back from the selected date.

    We can use variables to allow users to select a date that will then limit the data shown. In this case, we are being asked to set it up so that the previous 28 days of data is shown based on the user selected date.

    Step 1-1: Create a variable (SELECTED_DATE) that will be used as a control to let users select a date.

    Step 1-2: Create another variable (USE_SELECTED_OR_ALL) that will be used as a control to let users toggle between selecting a date, and showing all data. Set up 2 options (Selected Date & All Dates) for this variable.

    Step 2: Use the SELECTED_DATE and USE_SELECTED_OR_ALL variables in a case statement. This case statement will be used as a filter in Analyzer. I called this Beast Mode (RELEVANT_DATA_FILTER)

    CASE
    WHEN USE_SELECTED_OR_ALL = 'Selected Date'
    THEN
    CASE WHEN date >= DATE_SUB(SELECTED_DATE,interval 28 day) and date < SELECTED_DATE THEN 'Include' ELSE 'Exclude' END
    ELSE 'Include'
    END

    Step 3: Use RELEVANT_DATA_FILTER as a filter on the card in Analyzer. You will want to select the value "Include" for the filter.

    Step 4: Put your card on a dashboard, and then load in the controls so that users can interact with them.

    If a user selects "All Dates" the card will show data for all dates. The cool thing here is that users will still be able to use the date range controls on the dashboard to control how the data is displayed (by day, month), and what range is displayed (current month, YTD, etc).

    If a user selects "Selected Date" they can then select the date from which they want to show the previous 28 days of data from.

    Note, you can change your variables default values. For example, you can set USE_SELECTED_OR_ALL to default to "All Dates" or "Selected Dates" depending on your use case.

    You could take this even further, and create another variable (DAYS_TO_EVALUATE) that would let users dynamically change the number of days from the selected date that are shown. To do this, you would simply replace the hard-coded value of 28 in the case statement with your variable, and then include that variable on your dashboard as a control.

    CASE
    WHEN USE_SELECTED_OR_ALL = 'Selected Date'
    THEN
    CASE WHEN date >= DATE_SUB(SELECTED_DATE,interval DAYS_TO_EVALUATE day) and date < SELECTED_DATE THEN 'Include' ELSE 'Exclude' END
    ELSE 'Include'
    END

    Hope this is helpful to anyone who reads it! Feel free to reach out directly via message with any questions ๐Ÿ˜

    David Cunningham

    ** Was this post helpful? Click Agree ๐Ÿ˜€, Like ๐Ÿ‘๏ธ, or Awesome โค๏ธ below **
    ** Did this solve your problem? Accept it as a solution! โœ”๏ธ**

Answers

  • ColemenWilson
    Options

    Date - INTERVAL 28 DAY

    Then, use the new date field above in your visualization, but filter using the original date field.

    If I solved your problem, please select "yes" above

  • pauljames
    pauljames Contributor
    Options

    @ColemenWilson

    CASE WHEN MAX(Date) - INTERVAL 28 Day THEN pos_quantity_this_year ELSE 0 END?

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • ColemenWilson
    Options

    Ah I read your question wrong. When I've done this in the past, it has been static. I've done things like this:

    CASE WHEN `date` >= CURRENT_DATE() - INTERVAL 28 DAY AND `date` <= CURRENT_DATE() THEN `value` ELSE 0 END

    I can't think of a great way to make it dynamic. Hopefully @GrantSmith @MarkSnodgrass or @MichelleH have an idea.

    If I solved your problem, please select "yes" above

  • david_cunningham
    Options

    @pauljames - you can use variables to accomplish this.

    So you would set up a variable that you could put on a dashboard as a control.

    Then you would create a beast mode that references this variable (above)

    You can see that above our data currently starts at 1/30 and goes back 28 days.

    If I switch the date to 2/29, you can see that the data changes to start at 2/28 and goes back 28 days.

    David Cunningham

    ** Was this post helpful? Click Agree ๐Ÿ˜€, Like ๐Ÿ‘๏ธ, or Awesome โค๏ธ below **
    ** Did this solve your problem? Accept it as a solution! โœ”๏ธ**

  • pauljames
    pauljames Contributor
    Options

    @dlc3 , what does the beast mode look like then that references the 28_DAYS_FROM_VARIABLE beastmode?

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • david_cunningham
    edited April 25
    Options

    @pauljames - that beast mode is included as an image above, but also pasting it below for your reference.

    case when close_date >= DATE_SUB(START_DATE,interval 28 day) and close_date < START_DATE then 'Include' else 'Exclude' end

    START_DATE is what I called the variable, and close_date is the dates you're filtering on with the control.

    You would then filter on that beast mode in Analyzer to "Include" to make sure only dates that are 28 days from the selected date are shown.

    David Cunningham

    ** Was this post helpful? Click Agree ๐Ÿ˜€, Like ๐Ÿ‘๏ธ, or Awesome โค๏ธ below **
    ** Did this solve your problem? Accept it as a solution! โœ”๏ธ**

  • david_cunningham
    Options

    @pauljames - I edited my message to include more context. I tried to post a video, but looks like I'm not allowed to. Feel free to message me directly and we can hop on a call for me to walk you through this.

    David Cunningham

    ** Was this post helpful? Click Agree ๐Ÿ˜€, Like ๐Ÿ‘๏ธ, or Awesome โค๏ธ below **
    ** Did this solve your problem? Accept it as a solution! โœ”๏ธ**

  • david_cunningham
    Answer โœ“
    Options

    I wanted to write up a more consolidated and easy to follow answer for anyone else curious about how to do this. This answer also expands on the requested functionality, by adding in a toggle that will allow users to flip between limiting the data based on a selected date, and showing all data. As well as allows users to specify the number of days to calculate back from the selected date.

    We can use variables to allow users to select a date that will then limit the data shown. In this case, we are being asked to set it up so that the previous 28 days of data is shown based on the user selected date.

    Step 1-1: Create a variable (SELECTED_DATE) that will be used as a control to let users select a date.

    Step 1-2: Create another variable (USE_SELECTED_OR_ALL) that will be used as a control to let users toggle between selecting a date, and showing all data. Set up 2 options (Selected Date & All Dates) for this variable.

    Step 2: Use the SELECTED_DATE and USE_SELECTED_OR_ALL variables in a case statement. This case statement will be used as a filter in Analyzer. I called this Beast Mode (RELEVANT_DATA_FILTER)

    CASE
    WHEN USE_SELECTED_OR_ALL = 'Selected Date'
    THEN
    CASE WHEN date >= DATE_SUB(SELECTED_DATE,interval 28 day) and date < SELECTED_DATE THEN 'Include' ELSE 'Exclude' END
    ELSE 'Include'
    END

    Step 3: Use RELEVANT_DATA_FILTER as a filter on the card in Analyzer. You will want to select the value "Include" for the filter.

    Step 4: Put your card on a dashboard, and then load in the controls so that users can interact with them.

    If a user selects "All Dates" the card will show data for all dates. The cool thing here is that users will still be able to use the date range controls on the dashboard to control how the data is displayed (by day, month), and what range is displayed (current month, YTD, etc).

    If a user selects "Selected Date" they can then select the date from which they want to show the previous 28 days of data from.

    Note, you can change your variables default values. For example, you can set USE_SELECTED_OR_ALL to default to "All Dates" or "Selected Dates" depending on your use case.

    You could take this even further, and create another variable (DAYS_TO_EVALUATE) that would let users dynamically change the number of days from the selected date that are shown. To do this, you would simply replace the hard-coded value of 28 in the case statement with your variable, and then include that variable on your dashboard as a control.

    CASE
    WHEN USE_SELECTED_OR_ALL = 'Selected Date'
    THEN
    CASE WHEN date >= DATE_SUB(SELECTED_DATE,interval DAYS_TO_EVALUATE day) and date < SELECTED_DATE THEN 'Include' ELSE 'Exclude' END
    ELSE 'Include'
    END

    Hope this is helpful to anyone who reads it! Feel free to reach out directly via message with any questions ๐Ÿ˜

    David Cunningham

    ** Was this post helpful? Click Agree ๐Ÿ˜€, Like ๐Ÿ‘๏ธ, or Awesome โค๏ธ below **
    ** Did this solve your problem? Accept it as a solution! โœ”๏ธ**

  • ColemenWilson
    Options

    @dlc3 Amazing solution! Going to be using this for sure!

    If I solved your problem, please select "yes" above