How to set up a Future Date Filter
I'm trying to figure out how to set up a beast mode that will allow me to filter out historical dates while keeping the Date Range open to Current FY. I feel like I almost had it with the below calculation, but it's cutting off anything that is scheduled after this current week time period in future months:
case when year(current_date) <= year(`Target Date`) and week(CURRENT_DATE) <= week(`Target Date`) then 'Future' else 'Historical' end
I tried to replace 'week' with 'day' and then I'm only getting the last couple of days of the month.
Essentially, if I wanted it to work properly, I need it to show me data for every day after today, until the end of our fiscal year.
Not sure what I'm doing wrong, probably something obvious I'm just overlooking, but any help is appreciated!
Comments
-
Hi @jamesdII,
Have you tried just comparing the dates themselves?
CASE WHEN current_date <= `Target Date` then 'Future' else 'Historical' END
This treats a target date of today as in the future. (simply change <= to < to exclude today from the future group.
The other issue with your current logic is that both conditions must be true so for example if you had a date from January of next year it wouldn't consider it as a future date because the week check would fail even though the year is after the current year.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
Clarifying question. Do you actually have future dated data?
You could do something like date_format( `target_date`, '%Y-%m') to group your dates by yearMonth number and then take the count of each row, just to verify that you have future dated data.
Assuming you do, you can follow @GrantSmith 's tip with a small twist
If you only want data from the current fiscal year (assuming your fiscal calendar runs on a calendar year) then fix the pseudo-code, but this should work.
I used yearCurrent = yearTarget b/c you want THIS fiscal Year not all future dates.
case when year(current_date) = year(`Target Date`)
and CURRENT_DATE <= `Target Date` then 'FY Future'
WHEN year(current_date) < year(TargetDate) then 'Future'
else 'Historical' endIf your fiscal year does not align with the calendar year, I advise you look into using a Date Dimension table. The beast mode date functions (even with the offset calendar feature) will ALWAYS assume a calendar year.
take a look at
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K 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
- 748 Beast Mode
- 59 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
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 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
- 109 Community Announcements
- 4.8K Archive