5 Replies Latest reply: May 24, 2018 10:00 AM by Gabriele Cianci RSS

    STRANGER THINGS!!!

    Gabriele Cianci

      Hi,

       

      I'm creating a dashboard and I have a field "Timestamp". Format of this field is DD/MM/YYYY hh:mm:ss.

      In the script I used Date function to create a new field only with Date information without hour information. Then I have a second field "Day" with format DD/MM/YYYY.

      But when I create a filter with field "Day" for the same day I have 24 identical values but I want only a value. In other words, those 24 values correspond to different hours of the day. But for 1 day I want only a value (for example, for 1 March 2017 I want only the value 01/03/2017 and not 24 identical values and if I click on 01/03/2017 I want values for all the hours of the day infact to filter hours I have "Timestamp field").

       

      Possible solutions?

       

      Thank you at all!!!

        • Re: STRANGER THINGS!!!
          Petter Skjolden

          How did you create the new field. Formatting will not strip the field of the time component. You also have to actively remove the time component by using Floor() like this:

           

          Date( Floor(Timestamp) , 'DD/MM/YYYY') AS aDate

           

          The associative model of Qlik will take care of the rest so if you select a single date in the aDate field it will associate correctly with all the hours of that particular date. 

            • Re: STRANGER THINGS!!!
              Gabriele Cianci

              Thank you very much... If I add floor() function the filter is correct but why???

               

              Sorry for the title... Next time I will write a descriptive title!!

               

              Thank you

                • Re: STRANGER THINGS!!!
                  Gabriele Cianci

                  Maybe I understand. When I transform Timestamp field with Date() function the number of Timestamp values of a same day are different. With floor() function I transfom different values of a day in the same values.

                  • Re: STRANGER THINGS!!!
                    Petter Skjolden

                    Dates and timestamps are stored very similar to how Excel stores date and time. It has a numeric representation in addition to a formatting and display representation. The numeric representation is the number of days since the 31st of December 1899. So day #1 is 1st of January 1900. The decimal part represent the time during the particular dayand is the fraction of the day. so 1.5 should be noon on 1st of January 1900.

                     

                    Floor( Date ) removes the time from the date but it also removes the date format. That is why you need to use Date() to reapply the right format for the date or you will only see the numeric representation.

                     

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

                • Re: STRANGER THINGS!!!
                  Petter Skjolden

                  BTW: please try to put a descriptive title on your questions - so you follow the Community guidelines and common sense....