Group date

[#1]

Hi,

I've a problem with the date. I've many date/time records as 01/01/2011 22.33; 01/01/2011 22.45... and I want to create a calendar where if I select 01/01/2011 I show every date/time record.

The aim is an object that allows to select date (DD/MM/YYYY) in order to select all the date/time record that have the date selected.

How can I do?

Regards,

Fulgenzio

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

Create a new field in your load script so you have both the complete timestamp (with hours and minutes) and only dates:

Table:
LOAD ID,
     CompleteTimestamp,
     Date(Floor(CompleteDate)) AS OnlyDate // This will return only the date part (day month year) of the value
FROM ...

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

I believe that the best answer is to use a calculated dimension in the form:

=DayName([Date Time Field])

The other "xName" functions, like YearName, will also work for this purpose.  It gives you greater flexibility than denormalizing every date-time field in your script into every possible grouping of dates.

[Miguel_Angel_Baeyens]

Hi, and thanks for sharing!

But I'd note here that calculated dimensions in charts should be avoided because of performance, when possible. If you meant in the script, I agree. Besides, DayName() still keeps the hours and minutes value, although it represents only the date part, so the Floor() function is still needed to get rid of the time part.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#5]

I do not find that the Floor() function is necessary for the calculated dimension to accurately select records based on a date-time timestamp.  I am dealing with over 12,000 rows, and performance has not been an issue for using WeekName() as a calculated dimension.  How many rows are you calculating where you are finding performance issues?

[Miguel_Angel_Baeyens]

Hi,

If you need to work with the time part, you obviously don't need the Floor() function, as it was the case stated in this thread.

I'm working with many different volumes of data, from few million hundreds of thousands records to several hundreds of millions, but in any case, creating those dimensions in the script (if they are date related, in a master calendar, for example) improves performance always, since the QlikView doesn't have to evaluate the condition for each possible value of the dimension and later aggregate it depending on your expression. And as I mentioned above, I mean calculated dimensions in the Dimension tab of the chart Properties.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#7]

Yes, I follow you on the Dimension tab.  Still, Floor() is not needed, even when one does not need to work with the "time" part of the date-time timestamp.  At that many records, I can see how the calculation would help.  Thank you, Miguel.  I think you just saved me from a major headache that would have surfaced in the future.