Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
I assume the time values are similar to Excel's definition of TIMEVALUE:
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
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'
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.
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.
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.