Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Qlik Sense omitting Milliseconds

Hi Community,

I have a requirement for my current app to load the milliseconds of a time field, but for some reason qlik is omitting the milliseconds value. I've tried editing the variables in the Main tab in the loading script and also tried some shenanigans with the num#/timestamp# and time-functions.

Here's also an example of the database

Experiment NumberMachineDwell TimeWaiting Time
1100:01:02.527000:00:02.8145
1100:00:56.635400:00:02.4587
1100:00:42.458400:00:04.8537
1200:01:02.557400:00:03.6584

Usually i would ignore the milliseconds, but i have around 50 rows per machine, each nearly 1 second in milliseconds, which means i got a total difference of around 40 to 50 seconds.

Thanks in advance

Chris

10 Replies
Highlighted
Partner
Partner

Hi Christoph,

have you tried multiplying the timestamp? Like * 1000 makes seconds * 60 makes minutes etc.?

Highlighted
Creator III
Creator III

Yes, I've tried that, but with no success

Thanks for your reply

Chris

Highlighted
Partner
Partner

Does this not work?

=timestamp(Value,'MM/DD/YYYY hh:mm:ss.fff')

Highlighted
Creator III
Creator III

Sadly it does not.

I'll get a timestamp with milliseconds displayed but they are all zeroes.

For now, I went into the database and switched the column type from time to text and then loaded the data again and it kind of worked. Not sure how it worked now tho.

If anyone got an idea why this works, please help me out.

Kind Regards

Chris

Highlighted
Partner
Partner

try :

timestamp( timestamp#(Value,'MM/DD/YYYY hh:mm:ss.fff'),'MM/DD/YYYY hh:mm:ss.fff')

Highlighted
Creator III
Creator III

Your solution does not produce any outcome.

Skript:

2017-11-28 14_24_04-Qlik Sense Desktop.png

Data Model example values:

2017-11-28 14_25_22-Qlik Sense Desktop.png

Thanks for you reply anyway!

Regards

Chris

Highlighted
Partner
Partner

than maybe:

times( times#(Value,'hh:mm:ss.fff'),'hh:mm:ss.fff')


it all depends on how your field is written by default in the first place

Highlighted
MVP
MVP

If you look at your table you really don't have milliseconds there. Milliseconds have only 3 digits - your table has 4 digits. So what these timestamps have is 100 microseconds precision.

If you have this table as an inline table in Qlik Sense it will actually allow you to have the full 100 microsecond precision and interpret them as timestamps. But there is a catch. The field will keep the 100 microsecond precision on the individual value level. However when you do calculations and display in Qlik Sense it will truncate the non-millisecond digit.

I suspect however that your data comes from a database and are being read from the database as a timestamp and then it might be that the ODBC-driver enforce an exact ISO timestamp which is only milliseconds and not with 100 microseconds precision - truncating the last of the four digits?

Highlighted
MVP
MVP

You can circumvent the ISO timestamp/datetime interpretation in Qlik Sense by making some adjustments in your load script. The W4 field will be a pure numeric value not being limited to just milliseconds but can have additional fractions of milliseconds too. When displaying calculations with W4 you can always format it specifically as timestamp and Qlik Sense will happily round them to the nearest millisecond.

I have examplified it in the attached Qlik Sense App:

LOAD SCRIPT:

2017-11-28 15_19_54-Qlik Sense Desktop.png

DATA MODEL VIEWER:

2017-11-28 15_20_25-Qlik Sense Desktop.png

SHEET:

2017-11-28 15_20_39-Qlik Sense Desktop.png