Visualization of time based data YYYY-MM-DD-HH-MM

[gino2780]

Hello all,

i've got a qucik question on how to visualize my data consisting of a date field that has the date information of year, months, days, hours and minutes. The data are based on measuring counters of water consumption every half an hour (see sample data below).

You can also see that the consumption data has just slighty changes in the .xx range. The task is to visualize the minimal changes on water consumption per each time stamp. I've tried to use some sort of calender, but the problem is that the usual date function only considers YYYY-MM-DD. Such kind of date function is too rough for my task. Any suggestions?

[Colin-Albert · Accepted solution]

Here are some useful links on loading date & time data correctly into Qlik.

Why don’t my dates work?

Get the Dates Right

You will need to use a function such as

     Date#(Datum, 'DD.MM.YYYY hh:mm') as Datum

Note the case MM = Months   mm = minutes

[gino2780]

Thank you Mr. Albert!

That has worked quite well. Now, i want to combine the LOAD-list with the calendar function. I'm currently using the following calendar function (see below). Any suggestions on how i can adjust the calendar script from DD-MM-YYYY to DD-MM-YYYY hh:mm format?

I've tried to adjust the script lines:

Let Start = floor(date('01.01.2015','DD.MM.YYYY'));

Let End = floor(date('31.12.2016','DD.MM.YYYY'));

to my actual data

Let Start = floor(date('27.04.2016 05:00','DD.MM.YYYY hh:mm'));

Let End = floor(date('28.04.2016 04:30','DD.MM.YYYY hh:mm'));

but that didn't work (error message below):

Furthermore, but that has no high priority, i wanted to adjust the calendar script according to the date format, so that i can choose hour and minute based filters later on. That didn't work, too. Obviously because the script didn't even run to that point because of the error message at line: Date_src: LOAD...

All in all, i assume that my automatic calendar script is based on a DD-MM-YYYY format so that Qlik can't generate a more detailed calendar information for the DD-MM-YYYY hh:mm format.

[Colin-Albert]

That calendar script is creating a date calendar not is not time based.

The floor command will remove the time component from the date fields, so your hour and minute fileds will always contain zero.

I would suggest that you separate your timestamp fields to have two columns, one for date and one for the time component as this will be far more efficient in storage and RAM utilisation.

With separate date and time fields you can have a date calendar and then spilt time into hours and minutes quite easily.

[Colin-Albert]

I would suggest you create a new question for your time data, as this has been marked as answered so will have fewer views.

[gino2780]

Thanks again. I will probably open another thread for the time based request.

[gino2780]

Hello again,

i just wanted to mention that i've found a solution to select time based filters for my actual data:

i've just used the day, hour and minute function in the script by refering to my date field. Thus it's possible to select filters according to the given time intervall:

LOAD

    Day(Datum) as Tag,

    Minute(Datum) as Minuten,

    Hour(Datum) as Stunden,

.

.

.

[Anonymous]

Check out this example of loading hours and minutes Master calendar on timestamp