topic Re: Displaying long labels on chart X-axis in QlikView

Displaying long labels on chart X-axis

rwunderlich — Thu, 19 Apr 2012 01:50:10 GMT

Someone asked this question http://community.qlik.com/message/209386#209386 on the recent "Ask the Experts - Visualization" event. It was an intriguing question because I've run up against this problem before. The orginal thread is locked, so I thought I would post a solution here. The orginal question was:

Hi Experts,

What is the best way of showing long values on x-axis for charts since qlikview can't do wrap text on values?

Most of the time, the best we could show our client is to make it at 45 degrees angle or make the dimension on y-axis, horizontal chart. It doesn't look nice.

This is not very qool... don't you think?

Any suggestions? Thanks!

There appears to be no way to get the wrap to look noce in the UI. So my solution is to use a script SUB to create a new field that's broken into segments based on the first space after a minmum segment length. And then use the new field as the chart. It may look a bit daunting, but the only thing you need to modify the CALL statement at the end.

SUB SplitString (vField, vTable, vSegLen, vSplitField)

Split_temp:

LOAD *,

trim(mid($(vField), Start, Count-1)) as Split,

RowNo() as SplitId

;

LOAD *,

index( mid($(vField) & ' ', Start+$(vSegLen)), ' ' )+$(vSegLen) as Count

;

LOAD

$(vField),

if($(vField) = Previous($(vField))

,peek('Start')+peek('Count')

)as Start

RESIDENT $(vTable)

WHILE IterNo() <= div(len($(vField)),$(vSegLen))+1

;

LEFT JOIN($(vTable))

LOAD

$(vField),

concat(Split, chr(10), SplitId) as $(vSplitField)

RESIDENT Split_temp

WHERE len(trim(Split)) > 0

GROUP BY Title

;

DROP TABLE Split_temp;

END SUB

MyTable:

LOAD *, RecNo() as RecId INLINE [

Title

This is a long title that spans a long ways

This one is even very much longer then the previous long title

A shorter title

The rain in Spain

Healthcare / Services / Consulting / Accounting

]

;

// Call parms are Field, Tablename, SplitLength, OutputSplitField

// Split the string in "Field" at the first blank after segment length of "SplitLength".

// New field "OutputSplitField" will be joined to table "Tablename"

CALL SplitString('Title', 'MyTable', 15, 'SplitTitle');

-Rob

http://robwunderlich.com

Re: Displaying long labels on chart X-axis

Fri, 27 Jul 2012 17:10:01 GMT

I tried the following - made the dimension an expression be Replace(dimension,' ',chr(10))

This seems to create the multiple line labels, but for some reason QV does not display then horzontally or vertically (in vertical I see 2 lines, horizontal only one) - but in diagonal it works

So it seems that if I can figure out the option on the axis it may work, anyone knows what that option would be?

Re: Displaying long labels on chart X-axis

rwunderlich — Fri, 27 Jul 2012 17:52:52 GMT

It's all about the height and width of the chart. If you make the chart taller, you will see more lines in horizontal mode. If you make the chart wider, you will see more lines in the vertical mode. The number of lines will vary depending on number of dimensions and chart size. The diagonal is much more predictable.

-Rob

Re: Displaying long labels on chart X-axis

Fri, 27 Jul 2012 18:15:18 GMT

Rob,

I just tried to resize the chart with no luck - I resized the entire chart, and I also tried the internal element (with ctrl-shift) and I cant seem to display more than 1 lines in the labels, my labels are of the type "Mainstream Retired" or "Multi High Branch" so I expect 2 or 3 lines. Am I missing something

I see your code and I understand it, I am just not sure where or how to deploy it - is it on the load script? (my application uses a dynamix dimesnion that you can choose using a variable box, so I may need to adjust it to work with that)

am i right to assume that your code will have the same issue and I may end up with the diagonal ones

Thanks for any help

Re: Displaying long labels on chart X-axis

rwunderlich — Fri, 27 Jul 2012 20:44:25 GMT

I tried it out, and I can't get more than one line on the horizontal or two lines on vertical. Don't know if there is a way around it.

My code goes in the script to create a copy of the field with line breaks at n characters. Yes, you will need to use diagonal labels to get all lines to show.

-Rob

Re: Displaying long labels on chart X-axis

Fri, 19 Sep 2014 00:35:39 GMT

this could be helpful (in spanish)

Qlikview en Español: Ajustar Texto en Grafico Barras

Re: Displaying long labels on chart X-axis

Thu, 09 Oct 2014 09:48:55 GMT

Qlikview: Wrapping text in X Axis

http://scribbledbygb.blogspot.co.uk/2014/10/wrap-text-in-x-axis-qlikview.html

Re: Displaying long labels on chart X-axis

stevelord — Tue, 23 Dec 2014 17:10:30 GMT

This was exactly the approach I needed. Just had to change a long 3-word value in the dimension to a 2 line value that doesn't go off the corner off the graph. I did like this to tweak that one long value and leave the others alone:

=if(EmailInquiryType='Screenings and HAs', Replace(EmailInquiryType,'Screenings and HAs','HA and '&chr(10)&'Screening'), EmailInquiryType)

Re: Displaying long labels on chart X-axis

cbushey1 — Tue, 18 Oct 2016 18:00:29 GMT

Rob,

Not that I want to dig up an old thread here but your solution was what I have been struggling with for a few days.

I ultimately wanted to create a field that would look nice on a bar chart as the text of the field is pretty long.

What I found by using this code was that this function was very elegant and simple it how it was designed to allow you to take any field from any table and create a new, easier to read, label. I made a couple of edits that I wanted to point out because I think they improve the reusability of the code.

In your code, if the field you were parsing was exactly the same as another entry, the end result would group them together thus producing double the text in a single field. I opted to create a new variable (vGroupByField) that you can pass to the function that is a unique field that you want to group by. This will eliminate the double-text and give the intended result. I also realized that you had hard-coded the group by field so I edited this as well. Take a look.

SUB SplitString (vField, vTable, vSegLen, vSplitField,vGroupByField)

Split_temp:

LOAD *,

trim(mid($(vField), Start, Count-1)) as Split,

RowNo() as SplitId

;

LOAD *,

index( mid($(vField) & ' ', Start+$(vSegLen)), ' ' )+$(vSegLen) as Count

;

LOAD

$(vField),

$(vGroupByField),

if($(vField) = Previous($(vField))

,peek('Start')+peek('Count')

)as Start

RESIDENT $(vTable)

WHILE IterNo() <= div(len($(vField)),$(vSegLen))+1

;

LEFT JOIN($(vTable))

LOAD

$(vField),

concat(Split, chr(10), SplitId) as $(vSplitField)

RESIDENT Split_temp

WHERE len(trim(Split)) > 0

GROUP BY $(vGroupByField),$(vField)

;

DROP TABLE Split_temp;

END SUB

//MyTable:

//LOAD *, RecNo() as RecId INLINE [

//Title

//This is a long title that spans a long ways

//This one is even very much longer then the previous long title

//A shorter title

//The rain in Spain

//Healthcare / Services / Consulting / Accounting

//]

//;

// Call parms are Field, Tablename, SplitLength, OutputSplitField, UniqueGroupByField

// Split the string in "Field" at the first blank after segment length of "SplitLength".

// New field "OutputSplitField" will be joined to table "Tablename"

CALL SplitString('AccountFriendlyName', 'Accounts', 15, 'SplitTitle','AccountName');

Re: Displaying long labels on chart X-axis

rwunderlich — Tue, 18 Oct 2016 21:46:58 GMT

Cool. Thanks for the update. I might even pull this into Qlikview Components.

-Rob

Re: Displaying long labels on chart X-axis

cbushey1 — Wed, 19 Oct 2016 17:28:08 GMT

I realized that if the field you are grouping by and the vFieldName are the same field, when you try to load them into the first load statement of the Split_temp it will error. To get around this, I simply rename this field in the load and as a result rename it in the Group by clause.

So the final code should be this( bolded the two edits):

SUB SplitString (vField, vTable, vSegLen, vSplitField,vGroupByField)

Split_temp:

LOAD *,

trim(mid($(vField), Start, Count-1)) as Split,

RowNo() as SplitId

;

LOAD *,

index( mid($(vField) & ' ', Start+$(vSegLen)), ' ' )+$(vSegLen) as Count

;

LOAD

$(vField),

$(vGroupByField) as GroupByField,

if($(vField) = Previous($(vField))

,peek('Start')+peek('Count')

)as Start

RESIDENT $(vTable)

WHILE IterNo() <= div(len($(vField)),$(vSegLen))+1

;

LEFT JOIN($(vTable))

LOAD

$(vField),

concat(Split, chr(10), SplitId) as $(vSplitField)

RESIDENT Split_temp

WHERE len(trim(Split)) > 0

GROUP BY GroupByField,$(vField)

;

DROP TABLE Split_temp;

END SUB

Re: Displaying long labels on chart X-axis

paulwalker — Thu, 07 Jun 2018 11:16:45 GMT

HI ROb,

This code working perfectly..but its repeating the dimension text..

Please could you give suggestion

Re: Displaying long labels on chart X-axis

joanna22 — Tue, 22 Dec 2020 07:42:14 GMT

hi Rob,

When I try it in qlik sense. The title is broken to two or three lines in table, but it still showed one line in the Bar chart both vertical and horizontal.

Re: Displaying long labels on chart X-axis

ilyagolev — Fri, 07 Apr 2023 07:13:49 GMT

Yes, the same situation, in Qlik Sense I can't find a way how to show a dimension's title in two or more rows.

Re: Displaying long labels on chart X-axis

RanOuerg — Wed, 20 Nov 2024 16:54:32 GMT

is there any recent solution for this issue, i'm using qlik sense feb 2024 and i have the same issue with a bar chart i can't control the overflow and i need to see the whole text

Thank you in advance