General OBIEE

Get Involved. Join the Conversation.

Topic

    Stephanie Gott
    Convert Date FieldAnswered
    Topic posted November 12, 2019 by Stephanie GottSilver Medal: 2,000+ Points, tagged Analyses, OBI Answers, OBIEE Answers, OTBI 
    29 Views, 2 Comments
    Title:
    Convert Date Field
    Summary:
    Convert from MM/DD/YYYY to YYYY-Qx
    Content:

    Hello,

    I have a date field I want to convert. 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. 

    Thanks,

    Stephanie

    Best Comment

    Christian Berg

    Stephanie,

    I guess you got "Function Concat does not support non-text types" since you're working with numerical values whereas CONCAT is a string function. So just cast it.

    cast(YEAR(NOW()) as char(4)) || ' - Q' || cast(QUARTER_OF_YEAR(NOW()) as char(1))

    This gives you what you want for right now. Still - I'd do this in a proper time dimension if you use it a lot since it's always better to do things once centrally rather than X times for every single execution AND every single row that's being parsed.

    Comment

     

    • Christian Berg

      Stephanie,

      I guess you got "Function Concat does not support non-text types" since you're working with numerical values whereas CONCAT is a string function. So just cast it.

      cast(YEAR(NOW()) as char(4)) || ' - Q' || cast(QUARTER_OF_YEAR(NOW()) as char(1))

      This gives you what you want for right now. Still - I'd do this in a proper time dimension if you use it a lot since it's always better to do things once centrally rather than X times for every single execution AND every single row that's being parsed.

      • Stephanie Gott

        The Time dimension works for the current dates. I'm trying to get the quarter of the "Expected Close Date". The Secondary Date dimensions have a Quarter for the Actual Close Date. What I'm trying to do is show the Quarter of the "Expected Close Date" of Open Opportunities. It doesn't appear there is an out of the box dimension for that. If you know of one in Sales Cloud OTBI that I'm not seeing, let me know. Otherwise, your formula worked great by substituting "NOW" for the "Expected Close Date" dimension.. I appreciate your time. Thank you so much. 

        Stephanie