Screen Shot 2015-11-22 at 8.59.28 AM

Feature Geek: Filtering by Discrete Regular Aggregate Pills in v9.2

Tableau version 9.2 is getting close to release and thanks to a couple of little birdies — moles? informants? people whose initials are [redacted] and [redacted])? — I’ve learned about some features that weren’t announced during the keynote at the 2015 Tableau Conference. I asked for permission to talk about them and over the next few weeks I’ll blog about a few of my favorites.

NOTICE: This is a discussion of beta software and as such the described features and functionality are subject to change or complete removal prior to actual release. (Francois told me I’d get pie charts for Christmas if I didn’t put this warning here.)

The Problem

Up through v9.1 Tableau will happily put any continuous (green) pill on the Filters Shelf, but when it comes to discrete (blue) pills Tableau’s behavior has been inconsistent.

  • Discrete pills based on dimensions and record-level calculations like [Sales] > 10000 are supported.
  • Discrete pills based on regular aggregates such as SUM([Sales]) > 10000 have not been supported.
  • Discrete pills based on table calculations such as LOOKUP(SUM([Sales]) >10000, 0) are supported.

Here’s an example where I’m looking at Sales & Profit in my pirate-themed shops in New England. I’ve set up a KPI with the following formula:

IF SUM([Profit]) > 5000 THEN
    "Thar be profit!"
ELSEIF SUM([Profit]) > 0 THEN
    "Need more booty"

In version 9.1 and earlier if I try to filter to see only the Arrr! circles to find the money-losing stores, I can’t drop the KPI pill onto the Filters Shelf:

9.1 discrete agg on filters

There have been a few workarounds for this by:

  • Brushing/highlighting via the Color Legend.
  • Using a LOOKUP() calculation as described in the table calc section above.
  • Converting the discrete KPI into numbers that can be used as a continuous filter like IF SUM([Sales]) > 10000 THEN 1 ELSE 0 END, then filtering for 1.
  • Using FIXED Level of Detail expressions to convert the aggregate measure into a dimension.

This seemingly arbitrary distinction between dimensions, regular aggregate measures, and table calculations confuses new users and the solutions can cause further complications. It’s a point of friction that to us as users doesn’t seem like it needs to be there at all, no matter the underlying complexity of the problem.

Engage Friction Reducers!

In version 9.2 (with a little Mapbox Pirates theme goodness) this issue is gone, *poof* like it never existed:

9.2 discrete agg on filters

So we users don’t have to keep in mind the distinction between calculation levels and pill types, we can filter away.

And the discrete aggregate pill is also completely usable as a quick filter:

9.2 discrete agg quick filter

…and can even be used as a scoped filter like continuous regular aggregate measures:

Screen Shot 2015-11-22 at 9.30.31 AM


For me as a calculation writer this is a nice new feature because it lets me express KPIs and other measures in their natural forms and work with them more easily. As a trainer my students are less confused and can focus more. Thank you, Tableau devs!

Tableau Public is running v9.2 already and you can see the filter by discrete regular aggregates workbook and download it to your v9.2 beta. (If you’re not running the beta, you can get it by contacting your Tableau sales rep).


Screen Shot 2015-11-21 at 7.47.54 AM

Getting rid of “Year of” for Dates

The Tableau blog reposted my Fast Headers for Single Measure Tables post and I got the following question from Pamela Ann in response:

Here’s the problem, when using YEAR(Date) on Rows Tableau is automatically inserting the “Year of” for the header:

Screen Shot 2015-11-21 at 7.47.54 AM

We could create a calculated field for YEAR([Date]) and then use an Exact Date for the pill, but there’s an even faster way.Using Tableau’s Custom Dates feature we can do that in a few clicks plus typing the name of our new date field:

make custom date


2015-11-16 13_46_16-Tableau - Book2

Fast Headers for Single Measure Tables

This is a little tip that started out with a “Tableau doesn’t do that” and then an “Ooh…it does…that’s so cool!” Here’s the problem: In a single measure table, I want the name of the measure column as a header. In this worksheet using the Coffee Chain data I want “Sales” to be on top of the Sales column:

2015-11-16 13_34_20-Tableau - Book2

Now there are a bunch of ways to work around this…if you really want to read a comprehensive list check out the gory details, but there’s a way that tops them all that I learned thanks to Alberto Bertollino. in the aforementioned thread. From the view above (that took two clicks to build), do one (ONE!) more click & drag operation to drop Measure Values over the column and voila!:

single column header via drag

Alternatively, if you like double-clicking, that works too!

single header via double click

As Steve Jobs used to say, “Boom.

Over the last four years this tip would have saved me at least a couple of hours of doing things like creating tables with all discretes and then resizing columns and twiddling with formatting. Multiply that times thousands of Tableau users and we’re talking person-years.

Got another time-saving tip? Share it in the comments below!

2015-10-16 11_01_00-Tableau Conference 2015 _ Las Vegas, Nevada October 19-23

#data15 – What I’m Looking Forward To

Fellow Tableau enthusiasts Joshua Milligan and Andy Kriebel have done their posts on what’s up for next week’s Tableau Conference, here’s what I’m looking forward to:

1. Hanging with my Tableau Tribe

I’m the only hardcore Tableau user at my day job (though we’re hiring! ) so getting to see old friends and make new ones each year is a highlight. If you see me, please do say hello!

Tableau Zen Masters 2012

Would you believe I’d never met these guys until a day or so before this pic was taken?

2. Devs on Stage

Toys! Toys! Shiny new toys! Though the Tableau Zen Masters do get the occasional sneak peek into what Tableau is developing, this year Tableau has been extra-quiet about what they will be revealing at the keynote on Tuesday morning. What will we be seeing? I don’t know! And I’m super-excited!

3. Tableau on a Shoestring: Successful Deployment on a Tiny Budget

That’s my session, I’m looking forward to it for a couple of reasons. First of all, I’m looking forward to being finished with it — doing the kind of genuine & real writing that I do is like shoving my hand down my throat to rip out my heart and other juicy bits then putting them down on display for the world to see. The outcome is always worth the effort for me, though. Second, I’m looking forward to sharing some lessons learned, both things that went well and mistakes I’ve made.

I don’t have the budget that Matt Francis does for promoting his session (this is Tableau on a Shoestring after all), so here’s my teaser:

I hope to see you at #data15!


PS: If you’re not going, check out the Virtual Conference where some sessions will be streamed, and you can watch last year’s sessions (many of the Tableau employee sessions are repeated from year to year) at Tableau Conference Television.

Screen Shot 2015-08-29 at 10.54.50 PM

Crosspost from DataBlick – Tableau Dynamic Parameters Using Alteryx

I’ve been hearing lots of good things about Alteryx as an ETL tool and have been looking forward to using it more. Here’s what I did the day after Alteryx training: built an introductory solution in Alteryx for dynamically updating Tableau parameters, the post is up at DataBlick.



Screen Shot 2015-08-04 at 10.31.06 AM

Can I Help You??

This post is a shameless plug, if you’re looking for Tableau tips you can use the links to right.

About every 10 days (SD ~7) I get an inquiry from someone around the world looking for Tableau talent. I’ve occasionally taken on an engagement, mostly I’ve had a list of consultancies and consultants I’ve forwarded inquirers to. Now my name is on the list and you can work with me: I’m joining DataBlick on a part-time basis.

You can get one-on-one or group training & support from me on whatever Tableau topics that I’m knowledgeable about (calculations, structuring data, making the transition from Excel to Tableau, table calculations, conditional formatting, LOD expressions, etc. etc.). If you’re wondering what I might be able to do for you, here’s what one long-time Tableau user said:

I sat in on Jonathan Drummey’s “Extreme Data Blending” session, and was amazed at the depths he’s plumbed in ferreting out the mysteries of how data blending works. Even better was the clarity of his presentation, making the complex and esoteric seem familiar and graspable. I’m now much better equipped to employ data blending to good effect than I was a day ago. — Chris Gerrard, Tableau Friction

And if I can’t deliver what you need, I know some good people and would be happy to make a recommendation!

I’m available from 5-7pm Eastern on Tuesdays or Wednesdays and 5-7am Eastern on Fridays — great for folks in Europe, Africa, and Asia. These are short-term engagements: Maybe you’re stuck on a specific problem, or need help building some Tableau skills, or maybe you want to help your team do some targeted training. If you’d like to set up a standing appointment for a regular review, I can do that too. Other folks at DataBlick are available for longer engagements, the appointment-based structure we’re calling Help me, DataBlick! is trying out a new & different way of assisting and supporting Tableau users and several of us have hours available.

Why DataBlick?

The simple answer is who wouldn’t want to call Anya A’Hearn, Joe Mako, Noah Salvaterra, and Chris DeMartini co-workers? Early in my career I was fortunate enough to have a mentor who told me to be around people who were smart in ways that I’m not. Every single one of the DataBlick team does amazing work and has changed the ideas of what’s possible in Tableau: Anya creating astounding designs; Joe rethinking the interaction between data, Tableau’s inner workings and the viz that we see (plus setting an incredibly high bar for kindness and generosity in the Tableau community); Noah building amazeballs visualizations; and Chris finding new ways to build network graphs. And besides their boundless creativity, they all have a couple of traits that I much appreciate: the stubbornness to stick with a problem to see it through and come out the other side with new learning and the desire to share that learning with others. I’m grateful to have them as colleagues!

The more complicated answer involves a spreadsheet with the family budget and the 10 year projections for our daughters’s college expenses. [If that’s not something you think about, then please consider yourself lucky!] I’m still at my day job and will continue blogging and writing about Tableau. Working with DataBlick supports my family and enables me to help other users (like you?) get to know my favorite piece of software (ever!).

If you’d like to set up a session with me or one of the other DataBlick consultants (I’m available starting today) you can book an appointment in 1 hour or 1/2 hour increments.


Counting Pairwise Similar Votes in Tableau

This is another post inspired by a Tableau forums thread. Given a set of survey data that is in a “tall” format with a record for each voter & item (survey question) with their vote the goal is to end up with the sum of matching votes for each pair of voters. So if John & Karen both voted ‘yes’ on the same question that would count as 1 for that question, and then all other matching votes for the questions that John & Karen answered would be totaled up and that number put in a cell for the combination of John & Karen, like so:

The easiest way to do this would be using a join; however the data is from an OData source and those don’t support joins. Also data from OData sources has to be extracted and Tableau doesn’t currently support joining across extracts. The original poster indicated that doing any ETL wasn’t possible, the desire is to have everything just work in Tableau. So we turn to some alternatives, read on for how to build this with and without joins.


The way I approach this kind of problem is first to understand the goal and understand the data. The data seems pretty clear, and the goal is to end up with a matrix defined by the voter on Rows & Columns. So however many records there are in the data we want to see N^2 values where N is the number of voters. Given that the data source is OData (so no custom SQL) my first thought was to use the No-SQL Cross Product via Tableau’s data densification. That would requiring densifying both the voters (to make the matrix) and the items (to do the comparisons for each voter/item) and my initial attempts got way too complicated way too quickly so I bailed out on that. I came up with a slightly modified solution involving Tableau data blends, however I’m going to go through this first using a join-based solution because it’s easier to describe some of the subtleties involved (plus that will work for many data sources) and then the second time around with the blend-based solution

Join Solution

In this solution I set up the data so it has everything we need – all the combinations of votes and voters – then all we need to do is count records. Since we want to set up pairs of voters for each item, I set up a self-join on item:

This gets us the 47 combinations of voters & votes in the data. Now we can set up a view with the Voter dimensions from the original and the join:

Note that there are some empty cells here: the pairs Tom & Steave and Tom & Toney didn’t vote on any of the same items at all. We’ll come back to this later.

We only want to count voters that had the same vote, so the following Pairwise Vote Filter calc will return only those votes:

[Vote] = [vote (Sheet1$1)]

With that on the Filters shelf, we can set up a view using SUM(Number of Records):

There’s a bunch of empty cells here, what if we want 0’s to show? We can use Format->Pane Tab->Special Values->Text, but that will only work where there is data and we know there are some cells that don’t have data. To get those cells to be marks we can take advantage of Tableau’s domain completion by having a table calculation address on one of the voter dimensions.

We can use a simple table calculation like INDEX() (the field is called Domain Completion Trigger) and the default Compute Using of Table (Across) will address on the voter (Sheet1$1) dimension, padding out the marks:

With that in place we can now build the final view for the join. I set Format->Pane tab->Special Values->Text to be 0, changed the Mark Type to Square, edited the color to use a custom diverging palette (starting at 0), and turned off “Allow labels to overlap other marks” to have Tableau auto-swap the text color so the darker cells have white text:

So the data didn’t have quite all the granularity that we needed for display and we had to turn on data densification with a table calculation to pad it out. In the next section we’ll use Tableau’s ability to do even more padding.

Blend Solution

This uses a different approach. In this case, we set up the view so it has all the marks that we need (but not quite all the marks we’d want), blend in the data for the each half of a pair of voters and then use calculated fields to compute across the data and  “paint” the right values into the marks. It uses the original data as a primary source and then the domain completion technique outlined in the No-SQL Cross Product post to effectively get the necessary marks, then uses two self-data blends to get the comparison data that take advantage of the fact that Tableau data blends are computed after densification. For more information on that, see the Extreme Data Blending session from the 2014 Tableau Conference.

Starting out I duplicated the Voter twice, naming one Voter (Rows) and Voter (Cols), then put those on Rows and Columns, respectively. We only see the 5 marks for the 5 voters:

Then we can use the same INDEX() calc to trigger domain completion:

We need to do the comparison at the level of voter *and* item, and for Tableau to compare across data sources the comparisons have to be done as aggregates, so that means that item has to be in the view. When we add Item to the view what we’re seeing in each cell is a mark for each time the the voter on Rows and Voter on Columns both had votes for the same Item. There are 47 marks here, just like the 47 rows we got from the self-join solution.

A problem here is that we lose some of the domain completion, we’ll work around that. (Where I’d tried to start was to do the second set of densification necessary domain complete on Item as well, but that got too complicated.)

The view just got a lot bigger here, that’s because of Tableau’s mark stacking behavior. We’ll fix that later with a table calculation filter.

Now we can set up a couple of self-blends by first duplicating the data source twice. It’s also possible to duplicate the data connection only (for example by directly connecting to the extract), however that requires more effort to set up. I named the duplicated sources Rows and Cols, and in each created calcs for Voter (Rows) and Voter (Cols), respectively. Then I could add in the Vote fields from each source and Tableau automatically blends the Rows source on Item, Voter (Rows) and blends the Cols source on Item, Voter (Cols). If I hadn’t named the fields the same then I could have used Data->Edit Relationships… Here’s a view showing for each voter pair the vote (Item), the votes from Rows, and votes from the Cols source:

This view lets us see what votes line up with what. So in row karen/column john, we can see that they both voted for items 21, 25, and 32, and had the same votes for each.

The next step is to build a test calculation for the view. Here’s the formula for Pairwise Similar Vote Test:

IF ATTR([blend Sheet1 (test_voting) (rows)].[Vote])
 == ATTR([blend Sheet1 (test_voting) (cols)].[Vote]) THEN 1 ELSE 0 END

We’re using ATTR() as an aggregation because a) that’s the default and b) we are comparing fields from two different sources and Tableau requires them to have some sort of aggregation applied.

In the view, we can see that the calculation is working accurately:

The Vote dimensions from the secondary are useful for checking the calcs, but they aren’t needed at this point so we can get rid of them:

Now to count up the votes in each cell. Here’s the Pairwise Similar Votes w/0 table calculation:

    WINDOW_SUM(IF ATTR([blend Sheet1 (test_voting) (rows)].[Vote])
      == ATTR([blend Sheet1 (test_voting) (cols)].[Vote]) THEN 1 ELSE 0 END)

This has a Compute Using on the Item so it partitions on Voter (Cols) and Voter (Rows). The inner IF statement is our same calc, those results get summed across all Items in each partition, and then the IF FIRST()==0 returns only a single non-Null value in each cell. Here it is:

We can then duplicate that view, make the marks Square, duplicate the Pairwise Similar Votes pill to the Color Shelf, set up a custom diverging color, duplicate the pill again to the Filters shelf to set it to filter for non-Null values, and we end up with this:

There are those empty holes where there are no Items for Tom & Steave and Tom & Toney. There’s no way that I know of using this particular blend to fill them in, because Item has to be a dimension in the view the domain completion is limited. This might be useful in some cases, I also came up with an alternative.

In this alternative instead of returning 0 when there are no pairwise similar votes the calc returns Null, here’s the revised Pairwise Similar Votes formula:

    WINDOW_SUM(IF ATTR([blend Sheet1 (test_voting) (rows)].[Vote])
      == ATTR([blend Sheet1 (test_voting) (cols)].[Vote]) THEN 1 END)

This has the same settings as the first, only now it won’t show any numbers. Then using the same process as before along with tweaking the color palette to start at 0 we can have a view that only shows where there are non-zero results, with white for everything else:


So there’s a couple of ways to go at this, the relatively easy way with a join and the more complicated way with the data blend. Personally, I’m in favor of voting up the Join Data from Different Sources feature request to allow joins across data sources, then even something like an OData source could be extracted twice and joined to create the desired view.

And the Tableau Public link: Pairwise Similar Votes.