Duration format cuts seconds

[Eddy]

Hello, 

I observe following behaviour in Qlik: 

My measures are durations, formatted as numeric in script. I want to show them in line diagramm and format as duration with hours and minutes. 

I observe that when fomat the measures as duration in format h:mm the seconds are just cut off, so when 2 min 59 Sec ist formatted as 2 min. 

I think a possible solution could be to format the measure as round(sum(Duration), 1/1440), 1/1440 is the step of the round function, represents a minute (24*60) 

• Is it the standard Qlik bevahiour to just cut off seconds when format duration  and h:mm is set?
• Is there some standard formatting setting which would not cut seconds, if I do not want to use the round function? 

Find the qvf attached. 

 

[rwunderlich]

Use 'hh:mm:ss' as your format. 

-Rob

[JuanGerardo]

Hi @Eddy ,

Take into account when you format, you are showing part of the different time components, not doing any mathematical operations like rounding. Rounding could be good for you, but not for others, I explain: imaging I want to round months, so above day 15th of any month you will see the next month, as of today (2021/04/16) you will see month May. This is not a standard, is just my business rule.

If you want to round the seconds before showing a duration in minutes, this is your business rule and you should implement in your calculations, for example with a Round() mathematical function before a TimeStamp() string conversion function. 

JG

[Eddy]

Thank you for you replies, 

my business requirement is just to show hours and minutes, not seconds. 

I have thought about rounding the expression like round(expr,1/1440*(100/60)))  where 1/1440 stand for minute and then I realised it should be accounted for decimal system when rounding, therefore multiplication with 100/60.

But that would not work if I have two measures in the diagramm like sum({<Type = {'A'}>} Duration) and  sum({<Type = {'B'}>} Duration), because they would be rounded separately and therefore their sum would not match to the KPI Object sum({<Type = {'A', 'B'}>} Duration). 

So I still have no solution hier, if the requirement is to show only hours and minutes, I kind of have to round the sum of measure A and B in the diagramm, not each measure separately so the sum matches the KPI, but it seems not possible to me...

[JuanGerardo]

Then, you can check if your expr has a second component greater than 29, and in that case add one second to your expression:

Date(expr + If(Second(expr) > 29, MakeTime(0, 0, 1), 0), 'hh:mm')

or if you don't want to use the If() statement, like me, you can use some maths: (24 * 60 * 60 = 86400 seconds/day)

Date(expr + Floor(Second(expr) / 30) / 86400, 'hh:mm')

JG

[Eddy · Accepted solution]

Ok, I got, it. So the formatting as duration [h:mm] just shows hours and minutes and disregards seconds, so no automatical rounding here. Here I attached a screenshot, which shows that. Nevertheless, there seems to be a scaling problem when using the format duration [h:mm], left bar chart on the screenshot, and can be seen in the attached qvf. 

Thank you for the explanation. JuanGerardo.