date() Function with Timestamp

[#1]

Hi all,

I'm using QlikView 9.0 to do a breakdown of the membership on this site, and I've run into a little bit of a snag using the date() function. The site logs new users' join date by timestamp, like this:

2009-06-11 18:42:48

I don't particularly care about the time, but I want to see how many folks joined on each day, so I wrapped that field in a date() function to strip away the time.

The problem is, all of the dates left behind are showing up as individual values, not the same value (i.e, 4/15/2009 shows up multiple times instead of once in the list box):

I imagine it's doing that because of the time that's omitted; since they are different, they are being added as different values.

Can someone please help me with the syntax needed to make these values the same, at least in QlikView? Thanks!

[rwunderlich · Accepted solution]

Hi Jason,

date() is a display formatting function, and does not change the underlying value. The fractional part, representing the time, is still there in each value.

The typical way to get just the date from a timestamp is the floor() function. e.g floor(mytimestamp) as mydate.

-Rob

[#3]

Rob, as always, you populate the forum with your wisdom. it worked perfectly! Thank you.

[johnw]

And similarly, use frac(mytimestamp) to get just the time. Floor(x)+frac(x) = x. I figure many people would want to extract both a date and time from a timestamp, even if you don't need it in this specific case.

[#5]

John,

That could be cool, to see which times of day people join the site. Thanks for your input as well!

[Anonymous]

Jason,
It's been a sort of a standard in our company to use date(floor(Field)) in the script. Besides preventing the problem like the one you had, it also makes the QV application smaller because of the small number of distinct values in the date field. Cant't be more than 366 per year. With the fractional part, virualy any record is distinct. Not good if you have millions of records.
Not the case with QlikCommunity users yet. But you can see the difference in the file size, I'm sure.