Case Statement - 28 Days from a specific date?
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!
Best Answer
-
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'
ENDStep 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
WHENUSE_SELECTED_OR_ALL
= 'Selected Date'
THEN
CASE WHENdate
>= DATE_SUB(SELECTED_DATE
,interval DAYS_TO_EVALUATE day) anddate
<SELECTED_DATE
THEN 'Include' ELSE 'Exclude' END
ELSE 'Include'
ENDHope 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! ✔️**1
Answers
-
Date
- INTERVAL 28 DAYThen, 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
0 -
CASE WHEN MAX(
Date
) - INTERVAL 28 Day THENpos_quantity_this_year
ELSE 0 END?IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!
0 -
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 ENDI 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
0 -
@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! ✔️**1 -
@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) andclose_date
<START_DATE
then 'Include' else 'Exclude' endSTART_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! ✔️**1 -
@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! ✔️**0 -
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'
ENDStep 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
WHENUSE_SELECTED_OR_ALL
= 'Selected Date'
THEN
CASE WHENdate
>= DATE_SUB(SELECTED_DATE
,interval DAYS_TO_EVALUATE day) anddate
<SELECTED_DATE
THEN 'Include' ELSE 'Exclude' END
ELSE 'Include'
ENDHope 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! ✔️**1 -
@dlc3 Amazing solution! Going to be using this for sure!
If I solved your problem, please select "yes" above
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 745 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive