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

  • GrantSmith
    GrantSmith Coach
    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!**

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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach
    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!**