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

    Date-Time formatting

      Hi all,

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


      Qualify *;


      LOAD  [submissiondate],




      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,


        • Re: Date-Time formatting
          Sunny Talwar

          Try this:






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

            • Re: Date-Time formatting

              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,


            • Re: Date-Time formatting
              Anand Chouhan

              Try this as calculated dimension




              1. First convert to date format

              2. Then use DayStart for the finding Daystart




              • 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.




                • Re: Date-Time formatting
                  jagan mohan rao appala



                  Try like this


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


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

                  Hope this helps you.



                  • 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

                      I would Create a New Section to the Load Script

                      So I would add something like :


                      Tag Field


                      With '$date';



                      DECLARE FIELD DEFINITION TAGGED '$date'


                            first_month_of_year = 1


                            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







                      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



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



                            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",