New Year Filtering Issues by Week / Month
My date filters are on the struggle bus in the new year y'all.
CASE when WEEK(`Week Date`) = Week(CURDATE())-1 then 'show' else 'hide' end = 'show'
The filter is simply returning this current week instead of the previous week. I assume this is because we are in Week 1, and the previous Week was 52.
What's a better formula for this that is solely focused on the week chronologically instead of the numeric representation?
(I know I am going to run into the same issue with months when I get to the next dashboard)
Answers
-
Try something like this:
- CASE WHEN (`Week Date` + INTERVAL (7-DAYOFWEEK(`Week Date`)) DAY) = (CURDATE() - INTERVAL (DAYOFWEEK(CURDATE)) DAY) THEN 'Last Week' ELSE 'NOT' END
The first part is is adding days to the week to get the last day in the week. The second half is calculating the end of the week based on the current date (for example Monday = 2 so it's subtracting 2 days to get to Saturday of last week)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thanks for your input on this. I got an error trying that out: Failed to convert 'NOT' from type 'string' to type 'boolean'
I also wanted to share more details- just in case they are contributing to any issues here.
The data is grouped and aggregated by week using the start date of each week before hitting this filter tile.
So, all of the dates passing into this filter are Sundays (the start of the week for this data set).
0 -
How are you utilizing the beast mode? Are you attempting to convert the value somewhere to a boolean?
Because you're using the first of the week you could simplify the beast mode to something like:
- CASE WHEN `Week Date` = (CURDATE() - INTERVAL (DAYOFWEEK(CURDATE) + 6) DAY) THEN 'Last Week' ELSE 'NOT' END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith , just in case it impacts this solution- this is an ETL filter tile and not a beast mode.
I can confirm I am definitely not converting this date field to a boolean.
0 -
@GrantSmith I think this is something glitchy in the ETL itself.
I used your formula and changed 'NOT' to null in a Formula Tile
CASE WHEN `Week Date` = (CURDATE() - INTERVAL (DAYOFWEEK(CURDATE) + 6) DAY) THEN 'Last Week' ELSE null END
I then used a filter tile to eliminate the nulls with IS NOT NULL. This did solve the issue, but there was something weird going on there as I got errors if I used a new tile instead of editing an existing one.
** So I have one last remaining question- and that is, how do I apply this for monthly data?
- Current date format uses the first day of each month (example 2022-08-01)
Thank you for your time on this- it is greatly appreciated.
0 -
You can use DAYOFMONTH instead of DAYOFWEEK
First of the current month:
- CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
I entered this, and 12-1-21 was processed as Null.
Is this because Jan is 1 and Dec is 12 numerically- or is something else going wrong here?
CASE WHEN `Month Date`= CURDATE() - INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY THEN 'Last Month' ELSE null END
0 -
Is 12-1-21 the value of your `Month Date` column? Is this a date column or a string column in your dataset?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith It’s a date column.
I had a typo there btw. It’s 12-1-22. I’m basically saying it should be viewed as the previous month but for some reason isn’t.
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
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 755 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive