2 Replies Latest reply: Mar 28, 2017 3:45 PM by Derick Chapman RSS

    Load Month and Year text fields as dates

    Derick Chapman

      I would like to load text fields Month and Year into QV in date format to more easily sort them in a chart.

       

      My month field is in long form (January, February, etc) and the year is typical 2017, 2018.

       

      Have been trying to implement the following code in my load script but am receiving no values when I pull my test metric into a straight table with project id.

       

      date(date#([Estimated Completion Month] & ' ' &[Estimated Completion Year] , 'MMYYYY')) as TestMetric

       

      Also tried

      date(date#([Estimated Completion Month], 'MM)) as TestMonthMetric

      date(date#([Estimated Completion Year] , 'YYYY')) as TestYearMetric

       

      I feel like I am simply misunderstanding how the date function works but could use some insight.

       

      The purpose of this task is to be able to sort a bar chart in correct date order. Was previously loading these fields as a concatenated value [Estimated Completion Month]&' '&[Estimated Completion Year] AS ECD but could not determine a way to order by year and then sort by month.. Will gladly take any tips on this issue as well.

       

      Thank you!