Tinkering with ‘ALL’ and ‘None’ in Titles

I named this blog Tableau Tinkering because tinkering is what I do when answering most Forum questions. I suspect most of us do. Many forum posts are questions describing very specific needs, that aren’t out-of-the-box obvious, or necessarily easy to do. In tinkering around trying to come up with an answer, I sometimes come across quirky Tableau behavior. It’s probably only ‘quirky’ to my way of thinking, and Tableau has a perfectly legitimate reason for behaving the way it does. But I find the following behavior a bit odd….

Aaron Tee posted a question today asking about the ‘ALL’ result that can show up when we insert a field into a title. If you use fields in Titles (or captions) you’ve probably come across this. Here was Aaron’s basic set up:

All v None

The basic problem he was having was if he unchecked the (All) box in the filter, so no items were showing, the count in the title would change to ALL:

All v None - 2

He wanted to change the ‘All’ to a zero. I tinkered with many different ways to attack the issue, and finally came up with a reasonable option. I never did get a zero to show, but I did find that Tableau can be forced (encouraged) to display a ‘None’ instead of an ‘All’ when the viz is empty.

Let’s cut to the chase, here’s the solution:


Putting a SIZE() table calc on BOTH the Filter shelf and the Detail shelf and setting the compute using to Item1 (in this case), will get Tabeau to switch from ‘All’ to ‘None’ when no filter option is selected. Care must be taken, because if you only put the SIZE() calc on one of the shelves, then the title won’t change as expected.

Overall, it’s a bit of a quirky solution, but possibly useful in certain circustances.

Tinkering with Filtering vs. Hiding

On the Tableau Forums this week, Parv Chana asked a question about using the ‘Difference from…’ Quick Table Calculation. As you probably know, (by default) this calculates the difference between the current row and a previous row. Here’s a basic viz using it:


Parv’s question was how he could show a value for that first row (June). The reason there isn’t a value showing for June is that he had filtered out May, so there wasn’t a previous value to compare June to. Adding May to the viz will of course give him a value for June, but then we’ve just moved the problem up a row; now May is Null.


While that Null indicator can be hidden (right-click it), it is also the first hint at a solution.

There are three functions available in Tableau that deal with Nulls: ZN(), IFNULL() and ISNULL(). We’re going to use the ISNULL() function to get rid of that first row without filtering the data out of the view.

Here’s the overall view:


When you add a Quick Table Calculation to a viz, you may have noticed that the name of the pill doesn’t change, and a new field isn’t added to the Measures window. Tableau is just creating this field ‘on-the-fly’ so to speak. But we’re going to want to use this table calculation in another calculation so we need to give it a name, and create a new field that we can get at. To do this click Customize… and type in ‘Difference from Previous’, and click OK. (A new calculated field should have just appeared in your Measures window.)

Next we right-click our new field in the Measures window, and click Create Calculated Field. In the dialog box wrap the field in an ISNULL() function like this:

ISNULL( [Difference from Previous] )

(I called this calculation Use for Hiding.) Dragging this new field out on the Color shelf produces this viz:


Now right-click the True in the color legend, and click the ‘Hide’ at the bottom of the pop-up list. This gets rid of May, and we end up with the viz Parv was looking for.

To clean up a bit, you’ll want to drag the ‘Use for Hiding’ pill off the color shelf and drop it on the Detail shelf. This frees up the color shelf for something else. Here’s the final viz:


I’ve posted a workbook of this solution on this thread: http://community.tableausoftware.com/message/314094#314094

Tinkering With Custom Percentile Bins

Patricia Santillan posted a fun little brain-teaser on the Tableau Forums today. She had a sorted bar chart showing bus ridership by routes. It looked like this:


What she was trying to figure out was how to color the bars by percent of total. Easy, just a quick table calculation, right? Well she didn’t want a continuous measure that would color each bar slightly lighter. (That sort of coloring is redundant, and besides the bar length does a much better job of encoding this information.) Instead Patricia wanted three discrete colors, determined by percentile bins. And just for fun, she wanted non-uniform bins — specifically, Top 50%, Middle 25%-50% and Bottom 25%.

To work this out I used a simplified version of Richard Leeke’s quantile formula, which compares two table calculations: TOTAL() and RUNNING_SUM to set up thresholds Here’s the formula I used:

IF .5 * TOTAL(SUM([Cy Ride]))>=RUNNING_SUM(SUM([Cy Ride])) THEN 'Top'
ELSEIF .75 * TOTAL(SUM([Cy Ride]))>=RUNNING_SUM(SUM([Cy Ride])) THEN 'Middle'
ELSE 'Bottom' END

Essentially we are taking a percent of the total number of riders (in the first line 50% of the total) and comparing that to the running sum, so the color changed when that threshold is met. Here’s what the viz looks like:


You might notice I added a couple of other columns in there. That’s Jonathan Drummey’s fault. I have to admit I took far longer to work this out than I should have, because I didn’t do the step-by-step Jonathan Drummey process. He builds up calculations one small bit at a time so he is certain he knows what Tableau is returning for each part of the calculation. I was short-cutting it, (doing it in my head) and I kept trying to use .25 instead of the .75 in the above formula. It wasn’t until I broke the calc down into components and put them out in the viz that I realized my mistake. You can see that the running sum is, well increasing as it goes down the page (duh!). So to get the bottom 25% we have to trigger it when the running sum hits 75% of the total. [Click the image for clarity.]

Anyway, I hope you take away two things from this post: 1. There’s a lot of good work already out there, much of it in the Calculation Reference Library, which can be adapted to your particular need and 2. Jonathan’s process of building up calculations (especially table calculations) one step at a time, shouldn’t be skipped.

Here’s the original thread, which has the workbook these screenshots were taken from.

Tinkering With Week Dates

On the forms this week Emily Archbuckle wanted to aggregate her data by week, but instead of displaying the default beginning-of-the-week date, she wanted to display the end-of-the-week date — and she wanted her weeks to start on Monday, instead of the default Sunday.

After tinkering with this a bit, I discovered that we can combine the DATETRUNC() and DATEADD() functions to give her what she wants. The formula for doing this turns out to be:

DATEADD(‘day’,6, DATETRUNC(‘week’,[Order Date],’Monday’ ) )

So what’s going on here?

In the DATETRUNC() function, we are doing a standard aggregation, telling Tableau to group the data by week. We are also using the optional feature of this function, [start_of_week], to change it from the default ‘Sunday’ to ‘Monday’. All pretty straight forward.

Now for the interesting part. By wrapping this code in a DATEADD() function, we can in effect display any one of the seven days in that week, without changing how the week is aggregated. It just changes the date being displayed in the header. So the formula as written adds 6 days to a week starting on Monday, which displays all the end of week dates on Sundays:


This is a little confusing because Tableau start-of-week dates are normally Sunday. So lets do a different one. Let’s start the week on Wednesday and display the end of week Tuesday:

DATEADD(‘day’,6, DATETRUNC(‘week’,[Order Date],’Wednesday’ ) )

This will produce this chart:


And if for some reason you wanted to display the middle of the week you would just change the 6 to a 3 or 4.

Final Note: Changing the start_of_week value will changes the values in the bar chart because we are aggregating to a different 7-day period. Changing the incremental value (6) in the DATEADD() function will NOT change the aggregation, it only changes the header display.