14 Replies Latest reply: Mar 17, 2016 5:40 AM by Janna Rous RSS

    Date-Time formatting

    Janna Rous

      Hi all,

      I am importing data that includes date-time data, as follows:

       

      Qualify *;

      [MALN]:

      LOAD  [submissiondate],

      [start]

      ...

      [KEY]

      FROM ...

       

      [start] is a date-time with formatting 'DD/MM/YYYY  hh:mm:ss'

       

      I sense that QLIK Sense "sort of" recognizes this as a date, but not totally.

       

      I want to make a bar chart with this data, with the date along the x-axis (as the dimension), and the count of [KEY] as the measure.

       

      So I put the expression: =date(daystart([MALN.start],'DD/MM/YY')) for the x-axis.

       

      And it just gives me a null value for date "-" - so all entries in the table show up counted for "-" date.

       

      I've tried everything I could find in the QLIK Community for bringing the [start] in as a date (i.e., date#(start, 'DD/MM/YYYY  hh:mm:ss')), but I am at a loss.  If anyone can help out at all, or if you have any ideas, I would be so appreciative.

       

      Many thanks,

      Janna

        • Re: Date-Time formatting
          Sunny Talwar

          Try this:

           

          =Date(DayStart([MALN.start]),'DD/MM/YY')

           

          or

           

          =Date(Floor([MALN.start]), 'DD/MM/YY')

            • Re: Date-Time formatting
              Janna Rous

              Hi Sunny,

              Thanks for the correction. 

               

              Here's the thing - I'm pulling the data in from a .csv file. 

               

              When I import the data into a .xls file and then import the .xls into QLIK, Sense is able to read the date fine using that formula (as normal, I've done this hundreds of times)...

               

              But QLIK still won't seem to recognize the date when it pulls it from the raw .csv file.

               

              Any suggestions?

              Thanks again,

              Janna

            • Re: Date-Time formatting
              Anand Chouhan

              Try this as calculated dimension

               

              =Daystart(Date([MALN.start],'DD/MM/YY'))

               

              1. First convert to date format

              2. Then use DayStart for the finding Daystart

               

              Regards

              Anand

              • Re: Date-Time formatting
                Sangram Reddy

                Hi Janna,

                 

                Did you check if Qlik is interpreting the field as a date field? If so this has to addressed first.

                 

                Thanks,

                Sangram.

                • Re: Date-Time formatting
                  jagan mohan rao appala

                  HI,

                   

                  Try like this

                   

                  Date(Floor(DateFieldName),  'DD/MM/YYYY')


                  OR


                  Date(Floor(TimeStamp#(DateFieldName, 'DD/MM/YYYY  hh:mm:ss')), 'DD/MM/YYYY')


                  Hope this helps you.


                  Regards,

                  Jagan.

                  • Re: Date-Time formatting
                    BRIJESH GADHESARIYA

                    SET DateFormat='DD/MM/YYYY'; // You can set whatever format you want-date or timestamp

                     

                    Load * ,
                    Date(MakeDate(Year([start]),Month([start]),Day([start]),'$(DateFormat)') as StartModifiedDate

                    From Table...........

                    • Re: Date-Time formatting
                      Kevin Stephenson

                      I would Create a New Section to the Load Script

                      So I would add something like :

                       

                      Tag Field

                      [Start]

                      With '$date';

                       

                      Calendar:

                      DECLARE FIELD DEFINITION TAGGED '$date'

                         Parameters

                            first_month_of_year = 1

                         Fields

                            Year($1) As Year Tagged '$year',

                            MonthName($1) as Month Tagged '$month',

                            QuarterName($1) as Quarter Tagged '$quarter',

                            Date($1) as Date Tagged ('$date', '$day'),

                            Date(daystart($1)) as Day Tagged ('$date', '$day'),

                            WeekStart($1) as Week Tagged '$week',

                            Weekday($1) as Weekday Tagged '$weekday',

                            DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

                           

                      DERIVE FIELDS FROM EXPLICIT TAGS '$date' USING Calendar;  

                       

                       

                      this will then split "start" into a number of fields to choose from so it will make it split into

                       

                      Start.Calender.Day

                      Start.Calendar.Week

                      Start.calendar.Month

                      ect

                       

                      this makes it alot easier to make tables because than you can do it by month, Week, Year, ect it should work for CSV files as well.

                       

                      hope this helps

                      • Re: Date-Time formatting
                        bruno bertels

                        Hi

                         

                        May be you can try with a dual expression that's mixxing date and date# :

                         

                        Alt(

                            Date(Floor(Date#("start",'DD/MMMM/YYYY hh:mm')),'DD/MM/YYYY'),

                            Date(Floor(Date("start",'DD/MMMM/YYYY hh:mm')),'DD/MM/YYYY')

                            ) as "Date",