Scented Controls in Tableau
TC19 was sooo much fun last week!! Seeing old friends and making new ones makes this event my favorite of the year. I cannot wait for next year!!
During IronViz we all saw Lindsey Poulter build the awesome custom control using collapsible containers and parameter actions. Watching her set this up reminded me of a hack I built earlier this year and never shared with the community.
Showing more context alongside control values is informative and gives authors options to (imperfectly) sort the values in the control.
In the gif below every Customer Name is prepended by the total dollar value in Sales the customer has contributed and… a bar. How do we get those bars in the quick filter control?
Start simple
Evaluating the total sales per customer is a simple level of detail expression.
Because we will be using the returned values in a separate calculation and we want to maintain a specific format, we need to floor and cast the measure values using the following calculation.
Concatenating the resulting expression gives us a field that contains the revenue per customer and customer name.
Describing this calculation from the schema viewer shows us this.
We got the sales values in thousands for each customer, but:
- we don’t have the visual indication for those sales (the bars) and
- our customers are sorted alphabetically, not based on a measure (sales).
Binning
In order to create these bars we need to bin our customers in groups based on their spending with respect to customer that contributed the most.
This calc returns values from 0 to 1.
We can now create N bins and allocate customers to each bin. Let us, for instance, say we want 10 bins. To do that we can create a “whole” parameter that contains 10 “|” values, i.e. “||||||||||”. With the following calculation we incrementally return parts of the string parameter.
Customers that fall in bin #1 will be allocated a value of “|” while customers that fall in bin #2 will be assigned “||”. The customer that contributed the most overall gets assigned to bin #10, i.e. “|||||||||”.
The final step is prepending the Resolver calculation to the “Customers with bars.”
We can now “Show Filter” for this calculation and with the default sort set to “Ascending” we get the card to the left. Clearly, customers with the least sales are in the first bin. If, however, we change the sort to “Descending” we get back a control showing the customers with the most sales at the top, shown to the right. Getting closer…
Unicode Block Binning
Now that we know how we can bin and (kinda) sort, let’s leverage unicode magic and dump values into 8 bins. This time, instead of multiplying the “Proportion of Sales” calculation by 10 we will multiple by 8 and take the ceiling of the result. Then, instead of parsing the string parameter and returning N “|” symbols, we will return a different unicode box symbol, as shown below.
The great thing about this is that we do not need to invert Tableau’s default sort order for the calculation “Customers with bars.”
Caveats
Sorting values in a control based on unicode strings is a nice workaround for bin sorting but not for actual measure values, i.e. John Lee should be below Bill Shonely etc. It goes without saying that the measure value chosen to bin the Customer Names could be something different than Sales but for the sake of simplicity we stayed consistent.
Keep in mind that the queries we generate for this will not be ideal as we are generating a filter using the formatted values.
Nonetheless, this workaround helps you add more context to all your controls (Parameters, Quick Filter, Data Highlighters, Legends and soooon Sets!) and hopefully shows how a withered technology may shine in Tableau. ❤
