topic Re: I need to create a seamless Time Dimension, based on Start Time in QlikView

I need to create a seamless Time Dimension, based on Start Time

rustyfishbones — Mon, 03 Mar 2014 14:55:16 GMT

Hi All,

The above chart is a pivot table, The background color changes based on the task being performed

The times at the top are the start times of the task.

My question is how can I get a more seamless chart, basically I would like the chart to always have the 24 hours in a day but with the tasks colored where it fits in with the time of the day.

I hope that makes sense.

Regards

Alan

Re: I need to create a seamless Time Dimension, based on Start Time

Mon, 03 Mar 2014 14:56:40 GMT

Hi Alan,

Try to look into this by HIC.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/23/the-master-time-table

Re: I need to create a seamless Time Dimension, based on Start Time

rustyfishbones — Mon, 03 Mar 2014 15:07:53 GMT

This may explain it better,

you can see from this sample data that user JOS was picking between 8 am and 9 am, but it is not represented correctly in the chart from the original post, you can see it's green for picking but only for the start time until the start time of the next task, but I need it colored from 8 to 9, like the below mock-up

I hope that clears up what I am trying to do

Regards

Alan

Re: I need to create a seamless Time Dimension, based on Start Time

rustyfishbones — Mon, 03 Mar 2014 15:20:34 GMT

Hi All,

Should I be using a different chart type to achieve this?

Re: I need to create a seamless Time Dimension, based on Start Time

Mon, 03 Mar 2014 15:23:29 GMT

Alan,

Thomas is certainly right. That is perhaps the same problematic as a master calendar, but with time in a day.

Many times, the problematic is that we get an event between two dates, and this event must be "applied" to a single date (how many clients, contracts do I have a given day). Therefore, I would say:

1) create a dimension with a given time elapse : every 15 or 30 minutes

2) relate each event to this dimension (you will duplicate some lines)

3) do the chart (I did not understand the different colors, so, will you need also to split the numbers ?)

Fabrice

Re: I need to create a seamless Time Dimension, based on Start Time

rustyfishbones — Mon, 03 Mar 2014 15:48:39 GMT

Hi,

How do I create 1 minute Intervals and link them to the Start Time and End Time?

Still not sure sorry!

Regards

Alan

Re: I need to create a seamless Time Dimension, based on Start Time

hic — Mon, 03 Mar 2014 16:21:26 GMT

You can use a While loop and create reference minutes, very much the same as described in http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

HIC

Re: I need to create a seamless Time Dimension, based on Start Time

hic — Mon, 03 Mar 2014 16:33:28 GMT

Something along the following:

Load *,

Time(IntMinute/24/60) as Minute;

Load *,

StartTime_IntMin + IterNo()-1 as IntMinute

While IterNo() <= FinishTime_IntMin - StartTime_IntMin + 1 ;

Load

Floor(24*60*[Start Time]) as StartTime_IntMin,

Floor(24*60*[Finish Time]) as FinishTime_IntMin

From xxx ;

where all the fields containing 'IntMin' are integer minutes.

You would need additional code to handle intervals spanning over midnight though...

HIC

Re: I need to create a seamless Time Dimension, based on Start Time

Mon, 03 Mar 2014 16:35:28 GMT

Alan,

Assuming StrtTime and EndTime are valid time stamps:

Data2:
LOAD ID, Start,
Timestamp(Start+IterNo()/(60*24), 'hh:mm') as NewStart,
EndTime
Resident Data

While Start+IterNo()/(60*24) <= EndTime

;

IterNo() is a function starting at 1. You can IterNo()-1 to start at 0.

A day is between 0 and 1: 0,5 is 12:00. Therefore, 1 minute is 1/60*24

The loop is done until we reach the upper bound (here End Time)

Fabrice

Re: I need to create a seamless Time Dimension, based on Start Time

rustyfishbones — Mon, 03 Mar 2014 16:52:34 GMT

Thanks Henric,

I will try this and get back to you

Regards

Alan

Re: I need to create a seamless Time Dimension, based on Start Time

rustyfishbones — Mon, 03 Mar 2014 16:53:06 GMT

Thanks Aunez,

I will try this and revert

Thanks again

Regards

Alan

Re: I need to create a seamless Time Dimension, based on Start Time

Mon, 03 Mar 2014 17:05:02 GMT

Well,

try Henric's solution first. It is certainly more robust (I did not see he had answered). But you got the way to do it.

Fabrice

Re: I need to create a seamless Time Dimension, based on Start Time

IAMDV — Mon, 03 Mar 2014 17:16:36 GMT

Hi Alan,

I'm recommending this article by just reading the subject line. And also read Henric's post:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

and one more very useful post

http://iqlik.wordpress.com/2011/05/22/a-new-choice-of-armor-the-flag-matrix/

Thanks,

Re: I need to create a seamless Time Dimension, based on Start Time

rustyfishbones — Tue, 04 Mar 2014 08:32:49 GMT

Thanks Deepak