Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Times are being formatted incorrectly in visualisations

Hi,

I am thoroughly confused by this problem, and need some help to understand why Qlik Sense is behaving like it is! I apologise if this post gets a bit lengthy, but I've tried to include just the necessary parts as it applies to my use case. This only happens for this specific example.

I have a set of data stored in 'test.tsv':

starttimeendtime
2019-07-18T16:45:31+10:002019-07-18T16:52:00+10:00
2019-07-18T16:54:28+10:002019-07-18T17:01:00+10:00
2019-07-18T17:03:27+10:002019-07-18T17:10:00+10:00

 

I want to measure the time between a given endtime and the next starttime. To do this, I have the following data load script:

 

InitialTable:
LOAD
	"starttime",
	"endtime"
FROM [lib://source/test.tsv]
(txt, utf8, embedded labels, delimiter is '\t', msq);

TableFullProcessed:
LOAD
	RecNo() as [ID],
    timestamp(timestamp#(replace(left("starttime",19), 'T', ' '),'YYYY-MM-DD hh:mm:ss'), 'DD/MM/YYYY hh:mm:ss') as "start time",
    timestamp(timestamp#(replace(left("endtime",19), 'T', ' '),'YYYY-MM-DD hh:mm:ss'), 'DD/MM/YYYY hh:mm:ss') as "end time"
Resident InitialTable;

left Join
ElapsedTable0:
LOAD
	[ID],
    Peek([start time], rowno(), 'TableFullProcessed') as "next start time"
resident TableFullProcessed;

left Join (TableFullProcessed)
ElapsedTable1:
LOAD
	ID,
    if([ID] <> Peek([ID], -1, 'TableFullProcessed'), interval([next start time]-[end time],'mm:ss'), null()) as "time between"
resident TableFullProcessed;

drop table InitialTable;

 

 

Running this, the table TableFullProcessed appears to have correctly calculated the times.

image.png

However when I go to my visualisation a table I'm using to analyse it has the results:

image.png

Which is clearly not correct. This sometimes happens when graphing on a bar chart, but it is less consistent. Why is this happening? I want my visualisations to be in minutes and seconds (mm:ss).

1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: Times are being formatted incorrectly in visualisations

Wrap the time function around the expression:
time(Min([time between]), 'mm:ss')
to format not as a number

View solution in original post

1 Reply
Highlighted
Employee
Employee

Re: Times are being formatted incorrectly in visualisations

Wrap the time function around the expression:
time(Min([time between]), 'mm:ss')
to format not as a number

View solution in original post