Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

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

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:

demo1.PNG

and I use class()-function:

demo2.PNG
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:demo3.PNG

with:demo4.PNG

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

1 Solution

Accepted Solutions
MarcoWedel
Not applicable

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

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'))

QlikCommunity_Thread_253794_Pic1.JPG

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

18 Replies
sunny_talwar
Not applicable

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

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

Not applicable

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

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
Not applicable

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

Can you mock up some data?

Not applicable

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

I have upload .qvf file with mock-up data

sasiparupudi1
Not applicable

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

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
Not applicable

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

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')


Capture.PNG

Not applicable

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

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?

Not applicable

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

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:
demo5.PNG

sunny_talwar
Not applicable

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

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'))

Capture.PNG