topic Re: customized duration format in QlikView

customized duration format

Wed, 13 May 2015 12:10:10 GMT

Hello,

I have a data which contents a number of minutes (a duration). I would like to present it in a friendly way, and keep it numeric so I can build graphics.

The best result I manage to get is:

=interval(interval#(sum(DURATION_MINUTE) , 'm'), 'Dj hh:mm') which gives me 2j 11:34 for instance ('j' stands for 'day' in French)

What I would like is to add something like 'h' between the hours and the minutes. Ideally:

2j 11 h 34 min

But if I set the format to 'D j hh h mm min' the whole 'h' and 'm' characters are interpreted as format chars, not just chars to be displayed: which gives: 2 j 11 11 34 3in

Of course I can calculate the nb of days, hours and minutes, and build a concatenated string, but then it is not a number anymore. It could possibly be acceptable in a table, but no way to build a graphic.

Does someone know a solution to customize the output format?

Rgds

Pascal

Re: customized duration format

sunny_talwar — Wed, 13 May 2015 15:56:29 GMT

What if you use a dual function here?

Dual(withconcatfunction, withoutconcatfunction(actualtimestamp))

This will solve both your purpose, right?

HTH

Best,

Sunny

Re: customized duration format

MarcoWedel — Wed, 13 May 2015 19:06:50 GMT

Hi,

one example using the proposed dual()-function could be:

=Dual(Replace(Replace(Interval(Sum(DURATION_MINUTE)*'00:01:00','dj hh @ mm $'),'@','h'),'$','min'),Sum(DURATION_MINUTE)*'00:01:00')

hope this helps

regards

Marco

Re: customized duration format

Tue, 19 May 2015 12:20:00 GMT

That seems to be the good solution indeed. I didn't know this function. Very flexible!

Now, there is still a last issue: in the graphs, the scale is displayed with the numeric value (that's pretty logical as the graph itself is built with numeric), not the formatted value.

Do you see any way to force the scale to be displayed as the formatted value?

Rgds

Pascal