Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
smiling_cheetah
Creator
Creator

Interval function time formats

Hello everyone,

I've read help and checked community, but couldn't quite find the answer, so would appreciate any help.

How can I modify number formatting, such that Interval(timestamp1-timestamp2, 'D hh') would show not only show X XX,

but will add text, so that result looks like: "X days XX hours"

I was able to write arbitrary things in NUM using # signs, but them seem not to work in INTERVAL

Thanks in advance

1 Solution

Accepted Solutions
sunny_talwar

Try this

Replace(Replace(Interval(Now() - Today(), 'D@ hh#'), '@', ' days'), '#', ' hours')

View solution in original post

4 Replies
sunny_talwar

Try this

Replace(Replace(Interval(Now() - Today(), 'D@ hh#'), '@', ' days'), '#', ' hours')

smiling_cheetah
Creator
Creator
Author

Hi Sunny,

Thank you a lot, this solution worked perfectly in KPI object and in a table,

but then I tried to put a formula into a chart, and it stopped working

replace(replace(INTERVAL(AVG("timestamp1"-"timestamp2"), 'D@ hh#'), '@', ' days'), '#', ' hours')


My suggestion is that after two replaces output is viewed as a string and not a number and QS refuses to display that in a chart. I'd really appreciate you could suggest any workarounds.

(The only solution I've come so far is to get rid of replaces and not to use words with H or D in the format expression )

sunny_talwar

May be use Dual function

Dual(Replace(Replace(Interval(Avg("timestamp1"-"timestamp2"), 'D@ hh#'), '@', ' days'), '#', ' hours'), timestamp1 - timestamp2)

smiling_cheetah
Creator
Creator
Author

Thanks, now it works in charts

That's a useful dual function