Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

smiling_cheetah
New Contributor III

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

Re: Interval function time formats

Try this

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

4 Replies

Re: Interval function time formats

Try this

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

smiling_cheetah
New Contributor III

Re: Interval function time formats

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 )

Re: Interval function time formats

May be use Dual function

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

smiling_cheetah
New Contributor III

Re: Interval function time formats

Thanks, now it works in charts

That's a useful dual function

Community Browser