Case Statement - 28 Days from a specific date?

pauljames

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

david_cunningham

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 😁

ColemenWilson

Date - INTERVAL 28 DAY

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

pauljames

@ColemenWilson

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

ColemenWilson

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.

david_cunningham

@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.

pauljames

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

david_cunningham

@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

@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

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 😁

ColemenWilson

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