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 toany 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.2K Product Ideas
- 1.2K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 527 Datasets
- 2.1K Magic ETL
- 3K Visualize
- 2.2K Charting
- 441 Beast Mode
- 23 Variables
- 514 Automate
- 115 Apps
- 391 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 67 Manage
- 67 Governance & Security
- 1 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive