Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
Employee

## Floor It!

Have you ever tried to compare two dates that looked the same to find out that in fact they were different?  I was recently approached by a colleague who had this problem.  In their script, they were adding a flag when the date in a field was also the last day of the month.  Both dates were formatted to show the month, day and year (M/D/YYYY) but the flag was never true even when the dates appeared to be the same.  When working with dates, you may find that although two dates are formatted the same, the underlying values may be different.  To troubleshoot this, we used the Num function to get the numeric value of the two dates we were comparing in the script to see if the two dates were the same numerically.  Take a look at the table below for an example of how we resolved the issue.

Steps Date 1 Date 2 Notes

 Start with these dates 2018-10-05 05:16:50 10/5/2018 Format Date 1 to display the date like Date 2 is formatted Date('2018-10-05 05:16:50', 'M/D/YYYY')>>10/5/2018 10/5/2018 Is Date 1 = Date 2? 10/5/2018 10/5/2018 No, not equal Use Num() to see numeric value of dates Num(‘2018-10-05 05:16:50’)>>43378.220023148 Num(10/5/2018)>>43378 The numerical values of Date 1 and Date 2 are not the same Use Floor to round Date 1 down to just the date Num(Floor(‘2018-10-05 05:16:50’))>>43378 Num(10/5/2018)>>43378 Using Floor, is Date 1 = Date 2? Num(Floor(‘2018-10-05 05:16:50’))>>43378 Num(10/5/2018)>>43378 Yes, they are equal

So let’s explain what is going on here.  We started with 2 dates – one that had a timestamp and one that did not.  After formatting the dates the same, it was determined that the dates were not equal.  This is because the underlying numeric value of Date 1 still included the time even though the time was not visible after it was formatted as M/D/YYYY.  The numeric value of 2018-10-05 05:16:50 is 43378.220023148 while the numeric value of 10/5/2018 is 43378.  When looking at the numeric values, the value before the decimal point represents the date and the value after the decimal point represents the time.  To handle this, the Floor function was used.  According to Qlik Sense Help,

Floor() rounds down a number to the nearest multiple of the step shifted by the offset number.

Compare with the ceil function, which rounds input numbers up.

Syntax:

Floor(x[, step[, offset]])

Once the floor function was used, Date 1 was rounded down to just the date, giving it a numeric value of 43378 - the same as Date 2.

It is helpful to remember that dates have numeric values.  We often see dates formatted to meet our needs which is great but when we need to compare dates, we need to look beyond the displayed date and look at the numeric date.  The Date function controls how the date is displayed but it does not change the underlying value of the date.  In this example, the Floor function rounded the timestamp down to just the date.  The Ceil function works similarly except it rounds up.  You can also check out Henric Cronstrom’s blog titled Why don’t my dates work? for other date related issues you may stumble upon.  I hope you find this blog helpful and that it helps you quickly troubleshoot date comparison issues should they arise.

Thanks,

Jennell

MVP

Why not use DayName()? You get to preserve your date information and format but lose the timestamp. There's even a built-in offset option if you need it. WeekName(), MonthName(), and YearName() provide the same service if you're trying to compare assorted periods.

8,536 Views
Partner - Specialist

I must object to the use of Floor/Ceil when it comes to dates.
Altough they give the correct result I much prefer the use of DayStart/DayEnd.

These return the correct datatype and as an added bonus allow offsetting.
You also have the same functions for Week, Month, Quarter and Year.
(Note: Ceil and DayEnd does not yield the same result as DayEnd is just before midnight
while Ceil will give you midnight, thus Ceil is the same as DayStart with offset +1)

When dealing with dates use the Date/Time functions.

8,536 Views
Partner - Specialist

Will not work if you are seven days off!

8,536 Views
MVP

I think you're confusing DayName() with something else, perhaps WeekDay()?

Dayname() and Daystart() return the same underlying date value. The difference is that Dayname() formats as a date, while DayStart() formats as a timestamp. From the official documentation:

DayName()

This function returns a value showing the date with an underlying numeric value corresponding to a timestamp of the first millisecond of the day containing time.

DayStart()

This function returns a value corresponding to a timestamp with the first millisecond of the day contained in the time argument. The default output format will be the TimestampFormat set in the script.

8,536 Views
Partner - Specialist

You are correct, my mistake.
But I would still prefer using DayStart().

8,536 Views
MVP

To each their own. When I use these functions, I typically do so because I don't need the time portion of a timestamp, so I almost always want DayName() - why format as a timestamp if I'm removing the timestamp? If your use case requires you format as a timestamp, DayStart() makes more sense, though. The same applies to the the week, month, quarter, and year Name/Start functions - it just depends on which output format you're looking for.

8,536 Views
Creator II

If you're creating an incremental master calendar, you have to use floor or daystart. Otherwise reload will top up server's ram memory.

6,874 Views
Contributor III

AWESOME!

2,859 Views
Partner - Specialist

Responding 3½ years later?
In response to the original post I would suggest using function DayStart() instead of Floor().
They do the same thing but DayStart() being a Date/Time function is more explanatory.

2,850 Views
Subscribe by Topic