Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gabriele_qlik
Contributor III
Contributor III

STRANGER THINGS!!!

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!!!

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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. 

View solution in original post

6 Replies
petter
Partner - Champion III
Partner - Champion III

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. 

petter
Partner - Champion III
Partner - Champion III

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

gabriele_qlik
Contributor III
Contributor III
Author

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

gabriele_qlik
Contributor III
Contributor III
Author

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.

petter
Partner - Champion III
Partner - Champion III

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' )

gabriele_qlik
Contributor III
Contributor III
Author

Thank you for your reply