Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gabriele_qlik
New Contributor II

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

Re: STRANGER THINGS!!!

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. 

6 Replies
MVP
MVP

Re: STRANGER THINGS!!!

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. 

MVP
MVP

Re: STRANGER THINGS!!!

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

gabriele_qlik
New Contributor II

Re: STRANGER THINGS!!!

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
New Contributor II

Re: STRANGER THINGS!!!

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.

MVP
MVP

Re: STRANGER THINGS!!!

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
New Contributor II

Re: STRANGER THINGS!!!

Thank you for your reply