topic Re: Time Dimension in QlikView

Time Dimension

Anonymous — Tue, 13 Nov 2012 22:07:06 GMT

Hi All, I'm trying to create a time dimension with the following columns.

Time#, Time, Hour, Min, Sec, Hour24, AM/PM

My issue is when creating the Time# column, the same time can be stored in many ways.

e.g. =Num(MakeTime(5,5,5)) => 0.21186342593865

=Num(Time('5:05:05')) => 0.21186342592593

Looks like I need to truncate to the x's decimal point before I store the Time#, has anyone figure out what that decimal point is?

Other suggestions also welcome.

Thanks,

Gene

Re: Time Dimension

Tue, 13 Nov 2012 22:52:35 GMT

Hi,

I assume the time values are similar to Excel's definition of TIMEVALUE:

Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Testing my theory with Qlikview:

=num(time('12:00:00'),'##,##########') gives me 0.5

=num(time('0:00:00.001'),'##,##########') gives me 0,0000000116

Testing my theory with Excel:

=TIMEVALUE("12:00:00") gives me 0.5

=TIMEVALUE("00:00:00,001") gives me 0.0000000116

So my assumption was correct: Qlikview stores the time as portions of a full day, with 0.5 half a day.

Dion

Sr. BI Consultant

Re: Time Dimension

Gysbert_Wassenaar — Wed, 14 Nov 2012 19:18:37 GMT

You don't want to truncate decimals as those represent the time as a fraction of a day. There's also no need to create all these fields for time. You can create one time field and in the charts and tables use a function to display the time in the format you need. You've already seen the time() function. You can pass it a second parameter to specify the formatting. For example time(0.85,'hh:mm TT') will show '8:24 PM'

Re: Time Dimension

Anonymous — Wed, 14 Nov 2012 21:16:28 GMT

QlikView recommends that if possible do most of your functions on your load and not in charts.

Besides that, your solution still doesn't work when you're trying to join multiple tables on a Time field, given that the time fields is different in the tables. I attached an example to my original post; check it out.

Re: Time Dimension

Gysbert_Wassenaar — Thu, 15 Nov 2012 08:59:17 GMT

I very much doubt Qlikview recommends to duplicate data if all you want is to show the data in different formats in the UI. I think they'd recommend to use the expression Number Format Settings instead.

It does seem that maketime, timestamp and time do create different fractions. So a floor(MyTime,0.000000001) might be needed.

Re: Time Dimension

Anonymous — Thu, 15 Nov 2012 17:15:16 GMT

The question is not surrounding UI and display of fields but joining tables with a time dimension.

Imagine you want to use Hour as your dimension in a chart when your tables contain a time. What would you do? Create a Calculated Dimension? I would rather do it on the load and duplicate the data. Anyways we're digressing from the original question.

It does seem that maketime, timestamp and time do create different fractions. So a floor(MyTime,0.000000001) might be needed.

Now you're getting my point. Why did you choose to the 9th decimal point? I also chose to Round to the 9th decimal point but that's just a hunch based on the qvw I created and attached on my original post.

If anyone has a better understanding how Time works in QlikView, I would really appreciate your help.