Dynamic summary title based on date range

Saketh

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

GrantSmith

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.

MarkSnodgrass

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

   

   

GrantSmith

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.