Reporting and Analytics for Sales

Get Involved. Join the Conversation.


    Stephanie Gott
    Expected Close date as Quarter and Year
    Topic posted November 12, 2019 by Stephanie GottSilver Medal: 2,000+ Points, tagged Dashboard, Data Visualization, Reporting and Analytics 
    13 Views, 1 Comment
    Expected Close date as Quarter and Year
    How can I show the Expected Close Date as a Quarter and Year?


    Our Opportunities have an expected close date. I want to convert that from MM/DD/YYY to "YYYY-Qx". Any thoughts on how to do this? I've tried using the Calendar functions in the formula. They work, but don't get me the exact above format. I have to use two separate columns. One gets the quarter number so, in the example above, it would just say "1". The other column I'm able to get the year "2020". When I try do a Concat in the 3rd column, I get syntax error b/c I think it doesn't like to concatenate formulas apparently. Any help would be appreciated. 






    • Fernando Paes

      Hi Stephanie,

      I think you are close with the formula, but contact has 2 limitations:

      - Can only concat two strings at a time.

      - Can only concat strings so you need to CAST((expr) AS CHAR())

      Try this formula:

      CONCAT(CONCAT(CAST(YEAR("Opportunity"."Effective Date") AS CHAR(4)),'-'), CONCAT('Q', CAST(QUARTER_OF_YEAR("Opportunity"."Effective Date") AS CHAR(2))))

      Hope it works for you.