2 Replies Latest reply: Feb 9, 2017 7:37 AM by Ryan Minaker RSS

    Dates change to numbers on line chart?

    Ryan Minaker

      Hi,

      I have a puzzling issue with dates on a line chart switching to numbers. I've tried several different ways to correct this (mostly by making changes to the way the dates were specified and loaded in the load script), but with no luck...

       

      I'm using a master calendar script that I got from this forum a while ago and it works fine in every other instance except with line charts. See the image I've attached. This is the exact same data, but the line chart just won't show the dates as dates!

       

      Something else more puzzling is that one day this just started happening (this might have started with Qlik Sense 3x, but I'm not sure) seemingly randomly...

       

      Any help/advice would be appreciated and thanks!

       

      R.

       

      qlik-sense-dates.png

        • Re: Dates change to numbers on line chart?
          Joan Marty

          Hello Ryan,

           

          could you please tell me how are declared your date ?

               definition performed in loading script?

               throught the add data module ?

           

          In all case, it seem that your dates are not properly recognized.

           

          It can also come from your laptop system config like DD/MM/YYYY on your regional settings and file in YYYY/MM/DD for example.

            • Re: Dates change to numbers on line chart?
              Ryan Minaker

              Hi Joan,

               

              Sure. This is how the date is declared in the loadscript:

              • Date([Completion_Date] ,'MM/DD/YYYY') AS [CompletionDate]

               

              I've tried other variations on this all with the same results, such as:

              • Date(Date#([Completion_Date], 'YYYY-MM-DD') ,'MM/DD/YYYY') AS [CompletionDate]

               

              This is the master calendar script I'm using:

               

              ===================================

              QuartersMap:

              MAPPING LOAD * Inline [

              Month, Q

              1, Q3

              2, Q3

              3, Q3

              4, Q4

              5, Q4

              6, Q4

              7, Q1

              8, Q1

              9, Q1

              10, Q2

              11, Q2

              12, Q2

              ];

               

               

              Temp:

              Load

                             min([CompletionDate]) as minDate,

                             max([CompletionDate]) as maxDate

              Resident [Transcripts];

               

              Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

              Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

              DROP Table Temp;

               

              TempCalendar:

              LOAD

                             $(varMinDate) + Iterno()-1 As Num,

                             Date($(varMinDate) + IterNo() - 1) as TempDate

                             AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

               

              MasterCalendar:

              Load

                             TempDate AS [CompletionDate],

                             Week(TempDate) As Week,

                             Year(TempDate) As Year,

                             Year(Addmonths(TempDate,6)) as FiscalYear,

                             Month(TempDate) As Month,

                             Day(TempDate) As Day,

                             Date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

                             ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                             Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                             WeekDay(TempDate) as WeekDay

              Resident TempCalendar

              Order By TempDate ASC;

              Drop Table TempCalendar;