topic Re: How to create Time Buckets with DateTime in QlikView

How to create Time Buckets with DateTime

rustyfishbones — Mon, 24 Feb 2014 09:44:34 GMT

Hi All,

I have the following field StartDateTime and I want to create hourly time buckets like the image on the left.

I have tried the following

however, it's giving me hourly buckets, but for each day, so instead of getting the 24 hourly buckets I am getting 24 for each day.

I also tried using SUBFIELD Function to extract the time stamp, but I keep getting a result of 00:00

Any help would be appreciated

rustyfishbones — Mon, 24 Feb 2014 09:45:34 GMT

sorry like image on the right!!!

jonathandienst — Mon, 24 Feb 2014 09:51:14 GMT

This should do the trick:

Time(Class(StartDateTime, 1/24), 'hh:mm')

HTH

Jonathan

Edit: forgot to put the frac() inside the class!!

rustyfishbones — Mon, 24 Feb 2014 09:54:44 GMT

Hi Jonathon,

That gives me the time buckets, but again I am getting them for each day

if you notice the size of scroll bar on the image below, I have lots of hourly buckets instead of just 24

nagaiank — Mon, 24 Feb 2014 10:06:30 GMT

Try

Interval(Floor(Frac(StartDateTime)*24) / 24,'hh:mm')

hic — Mon, 24 Feb 2014 10:07:21 GMT

Then you need to cut away the integer part of the timestamp:

Time(Class(Frac(StartDateTime), 1/24), 'hh:mm')

HIC

rustyfishbones — Mon, 24 Feb 2014 10:15:45 GMT

Hi All,

The following worked for me

however I like the solution Henric provided best