How to convert both int-values in labels of class-function to time format?

[#1]

Hi,

I try to get a histogram (# instances of flights within some flight-length ranges).

If I express mi flight length as int (number of minutes), I get what I want:

and I use class()-function:

However, I would like express minutes ranges as 'hh:mm - hh:mm'

And closest what I can get is (by converting int-variable to 'hh:mm' variable) is next:

with:

I.e. it shows only left margin of bin, and do not show "-" sign for negative intervals.

How can I get those labels looking like "02:30-03:00" and for negative: "-02:00 - -01:30"?

[#2]

This is exactly what I needed.
Thank you very much.

Could you recommend a good paper book(s) on Qlik Sense (as I see a lot of them on amazon and chapters)?

[sunny_talwar]

I have mostly learned my stuff on the community itself so don't really know.... but in general, you can learn a lot from troyansky‌‌'s book Amazon.com: QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sen...

[#4]

Great!
Thank you very much.

[MarcoWedel · Accepted solution]

Hi,

maybe another solution could be:

=Dual(Interval(Floor(blk_time_hm,'00:30'),'hh:mm')&' - '&Interval(Floor(blk_time_hm,'00:30')+'00:30','hh:mm'),Floor(blk_time_hm,'00:30'))

I used Interval() instead of Time() because it also works with both negative and absolute values greater than 1 (day).

"Floor()" and "Class()" also should make no difference in this case because it's not used to format but only to floor the numerical value.

Dual() is used because it adds a numerical value to the text value returned by the string operation thus taking care of the correct order.

hope this helps

regards

Marco

[sasiparupudi1]

if(sign(SubField(class(blk_time_hm,'00:30'), '<= x <', 1))<0,

   '-'&(Time(SubField(class(blk_time_hm,'00:30'), '<= x <', 1) ,'hh:mm')),

       (Time(SubField(class(blk_time_hm,'00:30'), '<= x <', 1) ,'hh:mm'))

   )

&'-'&

if(sign(SubField(class(blk_time_hm,'00:30'), '<= x <', 2))<0,

   '-'&(Time(SubField(class(blk_time_hm,'00:30'), '<= x <', 2) ,'hh:mm')),

       (Time(SubField(class(blk_time_hm,'00:30'), '<= x <', 2) ,'hh:mm'))

   )

[#7]

Yes, It also produce the result that I needed, but with less complexity in the formula.
Thank you very much.

[#8]

Thank you very much, Sasidhar.
Probably, it could be an option as well, but so far, Marco's answer is the simplest.

[MarcoWedel]

You're welcome.

Glad it helped

regards

Marco

[Oleg_Troyansky]

Thank you, Sunny!