Interval function time formats

[smiling_cheetah]

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

[sunny_talwar · Accepted solution]

Try this

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

[smiling_cheetah]

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]

Thanks, now it works in charts

That's a useful dual function