Times are being formatted incorrectly in visualisations

[nougatitati]

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':

starttime	endtime
2019-07-18T16:45:31+10:00	2019-07-18T16:52:00+10:00
2019-07-18T16:54:28+10:00	2019-07-18T17:01:00+10:00
2019-07-18T17:03:27+10:00	2019-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.

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

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).

[Lisa_P · Accepted solution]

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