More ASCII and String Charts in Tableau

ASCII and String Chart Madness

In the spirit of creativity and thinking outside of the box, here are some charts you can make in Tableau using ASCII Characters and String Manipulations. 

Many users are familiar with creating Bar Charts in Tooltips using Andy Cotgreave's incredible article http://interworks.co.uk/blog/charts-in-a-tooltip/ . And maybe some people have seen my trick of creating a 100 Mark Unit Chart using ASCII characters http://growingupdata.blogspot.com/2016/03/tableau-tip-creating-100-mark-unit.html (Also posted on http://www.vizwiz.com/2016/03/tableau-tip-how-to-create-100-mark-unit.html). Both of these techniques center around using ASCII characters and String Manipulates to create a specific number of "String Characters" based on math. 

As a refresher, here is the basic formula.

1. A calculated field/parameter containing a series of ASCII Unicode Blocks : [BAR]

‘▮▮▮▮▮▮▮▮▮▮▮▮▮▮▮▮▮▮▮▮'

2. A Calculated Field that determines the % of Total Value : [Technology Sales]

ROUND( (SUM(IF [Category] = 'Technology' THEN [Sales] END) / SUM(SALES)) * 20 

3. A Calculated Field that returns a Substring of the Unicode Blocks based on % of Total Value

LEFT ( MIN([BAR]) , [Technology Sales] )

The last two steps can be repeated for each category, to give a seemless "Bar Chart" in Tooltip Look.

 

I always like this technique, it was simple and not too difficult to implement. And it gave my users a little more information they could drill in on. 

As much as I liked this, I've always wondered what else could be done. So after some experimenting (And a few beers) here are some of the alternatives I have discovered.

Remix of the Bar Chart in Tooltip Formula

Ok, so first thing on the list, a different way of doing Bar Charts in Tooltip. This basically is the same as above, but how we go about it is slightly different. I've always wanted a way creating charts, with a limited number of records, like creating Unit Charts when all I have available is aggregated data. Never really found a way of doing this other than by setting up a scaffold, or by using the method above. Below is an alternative.

 

REPLACE(SPACE(ROUND(SUM(IF [Category] = 'Technology' THEN [Number of Records] END) / SUM([Number of Records])  * 20)),CHAR(32), '█')

+ STR(SUM(IF [Category] = 'Technology' THEN [Number of Records] END))

Ok, let's break this one down.

While messing around with different string functions, I noticed that there was a function called SPACE()

You may not think anything of this function, but it is crucial to everything I'm about to show. Essentially, this function allows you to create a String of SPACES equal to a Numeric Value. So what right? Well, we know we can use this function to create a string of Spaces, so to create a string of ASCII characters, all we need to do is REPLACE() the spaces with those Characters.

Using the same in-line calc from above, we can create a consecutive number of Spaces that is equivalent to our % of Total. 

 SPACE(ROUND(SUM(IF [Category] = 'Technology' THEN [Number of Records] END) / SUM([Number of Records])  * 20))

Then we replace those Spaces with the ASCII Block  █. You can use ' ' instead of CHAR(32), listing it as the ASCII char is a personal preference.

REPLACE(SPACE(ROUND(SUM(IF [Category] = 'Technology' THEN [Number of Records] END) / SUM([Number of Records])  * 20)),CHAR(32), '█')

+ STR(SUM(IF [Category] = 'Technology' THEN [Number of Records] END))

Now, at this moment, this is really just a reworking of an old calc. Doesn't provide much added benefit. But it is an alternative way of doing it, and one that hopefully promotes other to think outside the box. 

Creating an Inline Bar chart as Dimension.

Slightly altering the calc above, one could also create a "Bar Chart" as a Text Label, or even Column/Row Dimension

 

REPLACE(SPACE(ROUND({FIXED [Category], [Sub-Category] : SUM([Number of Records])}/{SUM([Number of Records])}*200)),CHAR(32), '●')

This uses the same trick as above, except we replace the In Line Aggregated % of Total with Fixed LoDs to determine the number of "Marks" while returning it at a Dimensional level. Using FIXED LoD at the level you are reporting just allows you to use this field as other dimensions, without having to worry about Discrete aggregated measures conflicting. So we can create a Bar Chart and a Spark Line in a single worksheet. You can even add the Bar Totals at the end to provide reference.

REPLACE(SPACE(ROUND({FIXED [Category], [Sub-Category] : SUM([Number of Records])}/{SUM([Number of Records])}*100)),CHAR(32), '█')

+ STR({FIXED [Category], [Sub-Category] : SUM([Number of Records])})

Creating an Inline Dot Plot

This one is slightly different, but ultimately it is the same technique. 

'|'+ SPACE(ROUND({FIXED [Sub-Category]: SUM([Sales])}/{SUM([Sales])} * 200)) +'●'

+'$'+STR(ROUND({FIXED [Sub-Category]: SUM([Sales])}))

Here we use a Pipe Character to setup an easy base for the user to compare length to. Then we create a sequential number of Spaces based on the Percent of Total Calcs we were using above. Finally we add a Dot to the end, and add the Value as a string on the end. This can be used on the Row/Columns Shelf as a Dimension, or it can be split up using the techniques above to be used in a tooltip.

Creating a Hollowed Bar Chart 

NOTE* This one is not perfect, and has some issues regarding the Encoding of Length. I added to show possibility. Will update as I learn more.

This is the last one, and it is a little hackier than the ones listed above (Well more creative). 

To do this, we are going to create a calc very similar to the ones already written.

'|' + STR(ROUND({FIXED [Sub-Category]: SUM([Quantity])}))
+ IFNULL(SPACE(ROUND({FIXED [Sub-Category]: SUM([Quantity])}/{SUM([Quantity])} * 200)-LEN(STR(ROUND({FIXED [Sub-Category]: SUM([Quantity])}))))+'|','')

Here what we do is start a line with a Pipe Character, this sets up as the beginning of the Bar. Then we add the the Value as a String Character to simulate a Left Justified Text Mark. Having this, we add SPACES as we did in other calcs. The difference is, we need to subtract the Text Length, so that we don't over inflate the bars lengths. Finally we add logic to remove, or include an Ending Pipe Character to end the Bar.

But wait...how did I get the bottom line?

Well this goes back to the whole point of the post. All of these calcs are Text, and can be used in Formatting like other calcs. So how do we add the bottom line? We just underline the text

Finally, by putting it all together we can create a pretty cool (And useful) Dashboard in a single Worksheet!