topic distinct date in QlikView

distinct date

Wed, 16 Jun 2010 15:56:12 GMT

Hi, I have date column of format MM/DD/YYYY HH:MM:SS AM/PM (EX: - 6/16/2010 10:46:15 PM).

I want to add multiple box for Date column to allow user to select a date. But the issue is I have multiple rows with same date with difference in time. How do I ignore time and show distinct date in multiple box.

Thanks in Advance

distinct date

fernandotoledo — Wed, 16 Jun 2010 16:04:22 GMT

Hours and minutes are fractions of days. You should convert your date to a numeric value, round it then convert it again with a date format. Something like: date(floor(num(DATE)),'DD/MM/YYYY')

distinct date

Wed, 16 Jun 2010 16:09:19 GMT

Thanks a ton for the reply.

distinct date

stevedark — Wed, 16 Jun 2010 22:50:25 GMT

Hi there,

Handling as a numeric works fine, but I find that using the daystart function is a bit more readable:

DayStart(CreatedAt) as CreatedDate,

There are also functions for MonthStart and YearStart, which can also be useful if you want to aggregate down a bit, but still have the value as a date that can be compared to other date values.

Regards,
Steve