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"?

[sunny_talwar]

Would you be able to share your app to test this out?

[#3]

So far qlik-apps manipulates with clients "sensitive" data (such a flight numbers, delay info, ... ), therefore I might not be able to get an approval to share it (i.e. make it public)..

[sunny_talwar]

Can you mock up some data?

[#5]

I have upload .qvf file with mock-up data

[sasiparupudi1]

Time(Time#(Trim( SubField(Class(Time,30,'X'),'<=',1) ),'mm'),'hh:mm')

&'-'&

Time(Time#(Trim( SubField(Class(Time,30,'X'),'X <',2) ),'mm'),'hh:mm')

[sunny_talwar]

May be this

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

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

[#8]

Hi Sunny,

The formula complexity is impressive (I have to learn all of this), but this is what I like to get. Thank you very much.
However, one minor note: the sorting order on negative part of X-axis is a bit weird (-23:30-00:00, while it would rathre be expected -00:30 - 00:00). Could you advise something for that part as well?

[#9]

Thank you Sasidhar,

I tried this out, however on my side I see that negative part of X-axis is missing, and bins are not even:

[sunny_talwar]

Try this:

Dual(

If(SubField(Replace(class(blk_time_hm,'00:30'), '<= x <', '+'), '+', 1) < 0, '-', ' ') & Interval(SubField(Replace(class(blk_time_hm,'00:30'), '<= x <', '+'), '+', 1), 'hh:mm') & '-' &

If(SubField(Replace(class(blk_time_hm,'00:30'), '<= x <', '+'), '+', 2) < 0, '-', ' ') & Interval(SubField(Replace(class(blk_time_hm,'00:30'), '<= x <', '+'), '+', 2), 'hh:mm'),

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