5 Replies Latest reply: Jun 3, 2010 9:30 AM by Garyth Den RSS

    Date format functions not working

      Hi

      I am completely new to Qlikview (using personal edition 9.00.7469.8 SR4). Busy going through the end user tutorial and there is an [Invoice Date] field loaded as well as fields where the year and month are extracted from [Invoice Date] - IE: year([Invoice Date]) as Year and month([Invoice Date]) as Month;

      The problem is that the year and date function do not work on the data that is extracted from a text file as the date format is USA m/d/yy and my regional settings are yy/mm/dd. I have tried changing my regional settings to match the USA format of the data and nothing. I even try to use the date() and date#() functions but they seem to have no effect whatsoever on this field. As a result the year and month functions display 0 results / rows.

      Any ideas how I can work around this ?

      Here is the section of script:

      LOAD
      [Invoice Date],
      Year([Invoice Date]) as Year,
      Month([Invoice Date]) as Month,

      I have tried:

      date#([Invoice Date], 'yyyy-mm-dd') amongst other things

      but this seems to have no effect on the output at all.

      Please help.

       

       

       

       

        • Date format functions not working
          Jandr? Killian

          Hi

           

          Are you applying the Date#() function in the script?

          Thy doing it from the front end (Expressions). On a specific object.

          Thanks,

            • Date format functions not working

              Yes I have tried applying it in the script.

              I have just tried to apply it using an expression - although I'm not sure if the expression I am using is correct: I tried to enter Date#([Invoice Date], 'YYYY.MM.DD') as well as =Date#([Invoice Date], 'YYYY.MM.DD') into the expression field under the sort tab but no change occurs. There does not seem to be any other place to enter an expression in a listbox.

              I have also tried changing my regional settings (Using Windows XP) to USA (as mentioned in first post) but it makes no difference to any of these functions working.

               

              Could it possibly be a problem with not realising the data type is a date field and instead reading it as text ? It was imported from a text document.

               

               

               

               

                • Date format functions not working
                  azizsawadogo

                  Hi!

                  Do you have these variables in your script?

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   



                   

                   

                  SET

                   

                   

                   

                   

                   

                  SET

                   

                   

                   

                  You can change "DateFormat" with the format of your dates. May be this will resolve your problem. DayNames = 'Lun;Mar;Mer;Jeu;Ven;Sam;Dim' ;

                   

                   

                  TimeFormat = 'h:mm:ss TT' ;

                  SET

                   

                  DateFormat = 'M/D/YYYY' ;

                  SET

                   

                  TimestampFormat = 'D/M/YYYY h:mm:ss[.fff] TT' ;

                  SET

                   

                  MonthNames = 'Jan;Fev;Mar;Avr;Mai;Juin;Juil;Aou;Sep;Oct;Nov;Dec' ;

              • Date format functions not working
                Shivarama krishna K

                Hi,

                Check the system date settings also,if they are not in correct format as required change them to required and even change the default settings

                in Qlikview script window also..

                Regards,

                Ajay