A niche one today, but it does come up in some vizzes every now and then. Can you remember the forklift where we use placeholder values like “min(1)” or something similar just to draw a mark in a graph to than use for something else? Well, sometimes you might want to use a placeholder mark to actually put a label in a view. 

We want to create a bar chart, showing the [Sales] for the top 10 [Products] by [Category] in three different columns – one column of 10 product for every category. We also want the full [Product Name] to the left of the bar and, if they are particularly long, we want to wrap them or split them over multiple lines:

Couple of issues: 

1. We cannot put the [Product Name] on the row shelf, since there can only be one product for every row – we need three for every row. 
2. We could put the [Product Name] on the bar’s label instead, however they cannot be as nicely outlined to the left as this and a normal mark label cannot be “wrapped” anyway. 
3. We can put a placeholder value next to our SUM([Sales]) on the Column shelf and use this to put the [Product Name] on as label (ignoring some axis-scaling issues to overcome), but this still doesn’t “wrap” the label. 

We are going for option 3 as this seems to be close, we only need to figure out a way to wrap the label. Normally, dimensions on the Rows or Columns shelf will wrap if they don’t fit length wise. This will not happen with mark labels for reasons unknown. 

So, we need a few calculations to do the wrapping for us. It’s going to be fairly manual with a dash of regex. If anyone knows a clearer way, I’d very much like to know it. First, we need to decide how long every line of our wrapped [Product Name] is going to be and we are going to use a parameter for that, [Length before Break], and set it to 30 to start with. We can play with that value later. 

Next, we wrap this into our first calculation for the first row:

[Wrap 1] = TRIM(REGEXP_EXTRACT(LEFT([Product Name]+’ ‘,[Length before Break]),'(.*\ )’)) 

Quick translation: the inner “LEFT” bit takes the first 30 characters of a Product Name, but the cool bit of regex cuts this shorter to ensure the break happens on the LAST space character within our 30 characters for a clean break. That’s why we also add an extra space to the Product Name, to ensure there is always a LAST space after the final character. 

Now, we are basically going to repeat this four times in total to ensure that we can have up to four lines per Product Name:

[Wrap 2] = TRIM(REGEXP_EXTRACT(LEFT(TRIM(REPLACE([Product Name], [Wrap 1],”))+’ ‘, [Length before Break]),'(.*\ )’)) 

[Wrap 3] = TRIM(REGEXP_EXTRACT(LEFT(TRIM(REPLACE (REPLACE([Product Name], [Wrap 1],”), [Wrap 2],”))+’ ‘, [Length before Break]),'(.*\ )’)) 

[Wrap 4] = TRIM(REGEXP_EXTRACT(LEFT(TRIM(REPLACE(REPLACE(REPLACE([Product Name], [Wrap 1],”), [Wrap 2],”), [Wrap 4],”))+’ ‘, [Length before Break]),'(.*\ )’)) 

For every “wrap” we take of the previous “wraps” first, as we cannot say for sure beforehand where the actual break is going to be. It depends on the last space within every 30 character section, which in turn depends on the previous break in the previous 30 character section. 

Finally, we are going to wrapped this up into a single field for ease-of-use, where we include the line breaks in the calculation itself:

[Wrap All] = [Wrap 1]+IIF([Wrap 2]=”,”,CHAR(10)+ [Wrap 2])+IIF([Wrap 3] =”,”, CHAR(10)+ [Wrap 3])+IIF([Wrap 4]=”,”, CHAR(10)+ [Wrap 4]) 

The CHAR(10) function is adding a line break before every “wrap section,” but only if there is anything in there to prevent adding unnecessary line breaks. 

Finally, create a Rank calculation to Rank all products based on Sales:

[Rank Calc for Wrapping] = RANK_UNIQUE(SUM([Sales]),’desc’)  

Let’s put this all together in a view. 

Put [Category], a placeholder calculation MIN(0), and the SUM([Sales]) on the Column shelf. Add the [Wrap All] calculated field to the detail shelf on the “All” marks card. 

Add the rank calculation to the filter shelf and change the Table Calculation to Compute Using [Wrap All] only. Filter to the top 10. Add the same rank calculation to the row shelf and make discreet, hide the column as we don’t need it visible. 

Make sure the Placeholder axis’s marks card is set to Bar and fix the Axis to something like “0 to 3.” You’ll find that if you change this to Gantt, Shape or just Text, the text tends to stick to much to the left hand side of the cell, whilst a zero length bar create a bit more space between the left hand side of the cell and the text.  

But wait, there is more: My colleague and absolute Tableau Hack Superhero, Alfonso Vaca-Lubischer, has a mega-improved version of the wrapping calculation. Instead of having your “capture groups” in separate calculations, he has applied some more sophisticated regex to do all of this in a single calculation: 

[Wrap All Deluxe] = REGEXP_REPLACE([Product Name],”\s*(.{1,”+STR([Length before Break])+”}|\S{“+ STR([Length before Break])+”,})(?=\s+|$)”, “$1″+CHAR(10))  

Another advantage of this calculation is that you don’t have to figure out how many manual capture groups you need (and keep wrapping them in more replace functions). Double win! Thanks, Alfonso. 

Check out the full dashboard for this blog post here.