Times are being formatted incorrectly in visualisations
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':
I want to measure the time between a given endtime and the next starttime. To do this, I have the following data load script:
(txt, utf8, embedded labels, delimiter is '\t', msq);
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"
Peek([start time], rowno(), 'TableFullProcessed') as "next start time"
left Join (TableFullProcessed)
if([ID] <> Peek([ID], -1, 'TableFullProcessed'), interval([next start time]-[end time],'mm:ss'), null()) as "time between"
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).