Displaying long labels on chart X-axis

[rwunderlich]

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

                    ,1

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

[#2]

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?

[rwunderlich]

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

[#4]

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

[rwunderlich · Accepted solution]

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

[#6]

hi

this could be helpful (in spanish)

Qlikview en Español: Ajustar Texto en Grafico Barras

[#7]

Qlikview: Wrapping text in X Axis

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

[stevelord]

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)

[cbushey1]

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

                    ,1

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

[rwunderlich]

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

-Rob