14 Replies Latest reply on Jun 29, 2018 8:57 AM by Ken Flerlage

    Converting a string to date (10.1)

    Brian Sullivan

      Hi all,

       

      I have a dimension, Time Period, that pulls date data as a string into the format "yyyyMM".  I need to create a calculated field that pulls the month data from this dimension, but I am using a Teradata connection so I cannot use Dateparse.  I have inherited these dashboards and am rebuilding them with a new data source, so I can provide the old calculated field that was used to do this.  The data source was previously an Oracle database so Dateparse is used.

       

      MQY Denomination indicates if the measure is MTD, QTD, or YTD (0,3,4 respectively). 

       

      IF [M/Q/Y Denomination] = 0 THEN DATEPARSE("ddMMyyyy", "01" + STR(RIGHT([Period Number],2)) + STR([Fiscal Year]))

       

      ELSEIF [M/Q/Y Denomination] = 3 AND [Time Values] = "Q1" THEN RAWSQL_DATE("TO_DATE(TO_CHAR(%1), 'MM/DD/YYYY')", "03/01/" + STR([Fiscal Year]))

      ELSEIF [M/Q/Y Denomination] = 3 AND [Time Values] = "Q2" THEN RAWSQL_DATE("TO_DATE(TO_CHAR(%1), 'MM/DD/YYYY')", "06/01/" + STR([Fiscal Year]))

      ELSEIF [M/Q/Y Denomination] = 3 AND [Time Values] = "Q3" THEN RAWSQL_DATE("TO_DATE(TO_CHAR(%1), 'MM/DD/YYYY')", "09/01/" + STR([Fiscal Year]))

      ELSEIF [M/Q/Y Denomination] = 3 AND [Time Values] = "Q4" THEN RAWSQL_DATE("TO_DATE(TO_CHAR(%1), 'MM/DD/YYYY')", "12/01/" + STR([Fiscal Year]))

       

      My attempted solution:

       

      DATE(STR(RIGHT([Time Period],2)))

       

      Ex Data:

      201702

      201808

       

      I need to create this measure as a date, then use it in the "Columns" on a dual axis graph and define it as a "Month".  I cannot share the workbook as it is sensitive data, but I can try to provide more info if needed.

       

      Any help would be GREATLY appreciated as I have made numerous attempts with this and only managed to cause a timestamp problem with the data source.