Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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