Dynamic summary title based on date range
Hi ,
I am looking to display previous two quarters as a summary (e.g jan 2021 to june 2021) and I want this to be dynamic .
The issue with my current logic is that it always considers 6 months ago as starting date. For eg for August 2021 - it shows feb 202 to july 2021 while I need it to show jan 2021 to june 2021. Any help will be appreciated. Thank you
Best Answer
-
Hi @Saketh
Here's a more compact version utilizing modulo operations and date format
CONCAT( -- START -- +5 not 6 since it's inclusive DATE_FORMAT(DATE_SUB(CURRENT_DATE(), (MOD(MONTH(CURRENT_DATE()), 3) + 5) MONTH), '%Y-%b'), ' - ', -- END DATE_FORMAT(DATE_SUB(CURRENT_DATE(), (MOD(MONTH(CURRENT_DATE()), 3)) MONTH), '%Y-%b') )
Replace CURRENT_DATE() with whatever date field you want.
To break it down:
MOD(MONTH(CURRENT_DATE()), 3)
Divides the current month by 3 to determine how many months into the quarter we are.
DATE_SUB(CURRENT_DATE(), (MOD(MONTH(CURRENT_DATE()), 3)) MONTH)
This subtracts the number of months we're into the quarter from the current date so that it tells us the ending month.
Since we're looking at two quarters we subtract an additional 5 months from the end date to get the starting date.
DATE_FORMAT(DATE_SUB(CURRENT_DATE(), (MOD(MONTH(CURRENT_DATE()), 3)) MONTH), '%Y-%b')
Finally we format the date to only show the 4-digit year (%Y) and the abbreviated month name (%b)
Note, this is a simple version where the days won't actually be the first day of the month or the last day of the month. If you need that as well you could utilize the beast modes outlined in https://dojo.domo.com/discussion/52687/domo-ideas-exchange-beast-modes-first-last-days-of-the-month-week#latest before you do the date_format function.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
This would get you the starting quarter. You would follow similar logic to get the ending month.
CASE WHEN MONTH(DATE_SUB(`dt`, interval 6 month)) < 4 THEN CONCAT('1/1/',YEAR(DATE_SUB(`dt`, interval 6 month))) WHEN MONTH(DATE_SUB(`dt`, interval 6 month)) < 7 THEN CONCAT('4/1/',YEAR(DATE_SUB(`dt`, interval 6 month))) WHEN MONTH(DATE_SUB(`dt`, interval 6 month)) < 10 THEN CONCAT('7/1/',YEAR(DATE_SUB(`dt`, interval 6 month))) ELSE CONCAT('10/1/',YEAR(DATE_SUB(`dt`, interval 6 month))) END
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Hi @Saketh
Here's a more compact version utilizing modulo operations and date format
CONCAT( -- START -- +5 not 6 since it's inclusive DATE_FORMAT(DATE_SUB(CURRENT_DATE(), (MOD(MONTH(CURRENT_DATE()), 3) + 5) MONTH), '%Y-%b'), ' - ', -- END DATE_FORMAT(DATE_SUB(CURRENT_DATE(), (MOD(MONTH(CURRENT_DATE()), 3)) MONTH), '%Y-%b') )
Replace CURRENT_DATE() with whatever date field you want.
To break it down:
MOD(MONTH(CURRENT_DATE()), 3)
Divides the current month by 3 to determine how many months into the quarter we are.
DATE_SUB(CURRENT_DATE(), (MOD(MONTH(CURRENT_DATE()), 3)) MONTH)
This subtracts the number of months we're into the quarter from the current date so that it tells us the ending month.
Since we're looking at two quarters we subtract an additional 5 months from the end date to get the starting date.
DATE_FORMAT(DATE_SUB(CURRENT_DATE(), (MOD(MONTH(CURRENT_DATE()), 3)) MONTH), '%Y-%b')
Finally we format the date to only show the 4-digit year (%Y) and the abbreviated month name (%b)
Note, this is a simple version where the days won't actually be the first day of the month or the last day of the month. If you need that as well you could utilize the beast modes outlined in https://dojo.domo.com/discussion/52687/domo-ideas-exchange-beast-modes-first-last-days-of-the-month-week#latest before you do the date_format function.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 691 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 112 Manage
- 109 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive