Tag Archives: sets

Feature Geek: How to Have Sets with Your Secondary (9.2 Style) via Aggregated Booleans

We’ll talk about Sets in a bit, first we need to do a little forep discuss another one of the new features in Tableau v9.2: Min and Max for Booleans.

This post is the third in a series on new features in Tableau 9.2. It covers unannounced features in a version of Tableau that hasn’t been released yet so the features discussed here may change prior to release, especially if the folks at Tableau get a headache over my Sets puns.

In Tableau v9.1 and earlier we can only aggregate Boolean fields to do a Count or Count (Distinct). Here I’ve created a Xerox Flag calculated Boolean dimension with the formula CONTAINS([Item], 'Xerox') to identify all order items that include Xerox in the name:

Screen Shot 2015-12-07 at 5.50.26 AM

Tableau 9.2 adds MIN(), MAX(), and ATTR() as aggregation options that have a number of impacts on what we can do in Tableau. Read on to find out!

New Aggregations for Booleans

Here’s the new context menu for a Boolean dimension in v9.2 using that same Xerox Flag showing Attribute, Minimum, and Maximum have been added:

Screen Shot 2015-12-07 at 5.52.01 AM

To help show what the new aggregations do let’s start out with this screenshot with Customer Name and Xerox Flag as dimensions, in particular the three rows with blue Abc marks indicating there is data:

Screen Shot 2015-12-07 at 5.53.25 AM

  • Aaron Davies Bruce (Bruce) who only has False as a value for Xerox Flag indicating that he has only purchased non-Xerox item(s).
  • Aaron Riggs (Riggs) who has both True and False, so he’s purchased both.
  • Alan Briggs (Briggs) who has only True, so he’s only purchased Xerox item(s).

If I aggregate Xerox Flag as MAX(Xerox Flag) then we see that:

Screen Shot 2015-12-07 at 5.54.01 AM

  • Bruce has False because he has only the one value for Xerox Flag.
  • Riggs has only True, because the max of True and False is True.
  • Briggs has True because he has only the one value for Xerox Flag.

If I aggregate Xerox Flag as ATTR(Xerox Flag) then we see:

Screen Shot 2015-12-07 at 5.54.43 AM

  • Bruce has False because he had only the one value for Xerox Flag.
  • Riggs has * because he has both True and False.
  • Briggs has True because he had only the one value for Xerox Flag.

What can we do with these new aggregations of booleans? Here are some ideas:

Filter by Discrete Aggregate

In an earlier Feature Geek post I’d described how in 9.2 we can filter by a discrete aggregate. We can put MAX(Xerox Flag) pill onto the Filters Shelf, filter for True, and boom we’ve got only those customers who have purchased one or more Xerox items–including Riggs and Briggs:

Screen Shot 2015-12-07 at 5.56.11 AM

There is still one limitation here, we can’t drop ATTR(Xerox Flag) from a primary data source onto the Filters Shelf:

9.2 no attr from primary on filters

I suspect the reason why has to do with ATTR() being a special aggregation computed in Tableau whereas MIN(), MAX(), COUNT(), and COUNTD() are all computed in the data source.

However, when using data blending we can put ATTR(secondary dimension) on the Filters Shelf because of how data blending works – many aggregate filters on secondary sources are generally computed in Tableau, not in the data source, so Tableau already has that ability to filter on ATTR(secondary dimension). Here’s an example using Xerox Flag from a duplicated secondary source:

9.2 attr from secondary on filters

Note that Tableau is adding an extra Null value to the list of filter values because that is Tableau’s default behavior for secondary dimensions used as filters. Vote for Option to eliminate null value form Quick Filter on secondary data source field if you’d like to get rid of this.

Use in Conditional Filters

In v9.1 and earlier if we had a Boolean dimension and wanted to use it to create a cohort then we had to do an extra step that was often confusing to new users. For example if we want to only return Customers who have purchased a Xerox item we’d build a Conditional Filter using a By Formula: calculation like SUM(IF [Xerox Flag] THEN 1 ELSE 0 END) >= 1:

Screen Shot 2015-12-07 at 6.06.40 AM

The inner IF statement is evaluated for every row and returns 1 or 0, then those results are summed up for each Customer and then if that aggregated result is >= 1 then the Customer is returned:

customer name conditional filter

Having MAX() as an aggregation for a Boolean lets us get rid of the indirection of the SUM(IF… calculation and most of the typing as well since we can now specify the aggregation in the By Field: section of the view. All I had to do for this filter besides mouse clicks was type in “True”:

Screen Shot 2015-12-07 at 6.07.38 AMThat is quite a bit easier to describe to new users!

Boolean Dimensions from Secondary Sources in the View

In v9.1 and earlier we couldn’t place Boolean dimensions from secondary sources in the view. Here in v9.1 I’ve duplicated Superstore and I’m trying to use the Xerox Flag from the secondary as a dimension and Tableau isn’t letting me drop it on Columns:

9.1 no boolean from secondary on filters

The reason why is that “under the hood” Tableau effectively treats dimensions from secondary sources as ATTR(dimension) — that’s why we get * for dimensions from secondary, see my 2014 Tableau Conference session on Extreme Data Blending for more details. In v9.1 and earlier Tableau does not support ATTR(boolean dimension) so we couldn’t drop the boolean. I already demonstrated above how we can use ATTR(secondary dimension) as a filter, and in v9.2 now we can directly use secondary boolean dimensions in the view, here’s the Xerox Flag boolean:

9.2 boolean from secondary on columns

And we can use that Xerox Flag boolean dimension from the secondary as a filter:

boolean dim from secondary filter

Sets with Your Secondary (9.2 Style)

Awhile back I wrote a post on how to use Sets from secondary data sources. You see, ordinarily we can’t drag in a Set from a secondary data source, they are greyed out:

9.2 cannot directly use set from secondary

The workaround I’d come up with was a) to create a calculated dimension in the secondary source based on the Set that b) converted the boolean True/False of the Set into text or numbers that could be used as a dimension filter. For example this formula turns the Top N CC States Set from Coffee Chain into a calculated field of a usable data type:

in top n cc states calcAnd then the calc can be placed in the view, filtering for “In”:

Screen Shot 2015-12-07 at 8.09.40 AM
Step b) was necessary because Tableau would not let us directly use a dimension with the Boolean data type from the secondary. With Tableau v9.2 we get a little closer to being able to have Sets w/out interruption use Sets more directly. We still need step a) convert the Set into a calculated dimension but we no longer need step b).

Here in v9.2 I’ve taken the Top N CC States Set and created a calculated Top N CC States Dimension field that has the formula [Top N CC States] so it’s just passing the boolean True/False into a calculated dimension:

Screen Shot 2015-12-07 at 8.12.16 AM

And in v9.2 I can directly drop this calculated dimension as a filter:
9.2 set from secondary via dimension

So we can use Sets from secondary sources in v9.2 a little more easily than we could before.

Conclusion

Besides the big new features (Mapbox!!) Tableau continues to make iterative improvements in the ease of use of the software. Being able to aggregate booleans using MIN(), MAX(), and ATTR() just like we can other dimensions takes away the mental friction introduced by having to remember the data type every time we want to aggregate.

Tableau Public is running v9.2 already and you can see the aggregated booleans 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).

What’s New in Tableau 9.2?

Roughly two weeks after the public release of Tableau 9.2 I’ll be doing an online training on all the new features in Tableau 9.2 Desktop – this post is a sample of what you’ll be seeing in the course, a combination of how the feature works, where the edges are and how you might use it. Sign up below to get more info when the course is available!

Let me know about the Tableau 9.2 New Features training with Jonathan Drummey and DataBlick

How to Have Sets with Your Secondary (Data Sources)

Tableau 9.2 adds the ability to use boolean dimensions directly from secondary sources in the view and on the Filters Shelf so it simplifies this setup somewhat. Read How to Have Sets with your Secondary (9.2 Style) with Aggregated Booleans for details.

Tableau Sets can turn incredibly complicated interactions into a few mouse clicks that can reveal patterns in your data. However, if you’ve ever created a Set in a secondary data source and tried to use it across a blend in a  primary, this is what you see:

2013-10-11 00_02_28-Tableau - sets from secondary

Lots of grayed out items that can’t be touched. It’s like you’re at a party and have just met the hot-babe-of-appropriate-gender-of-your-dreams and they are totally into you, but only at the party, and not back at your place.

However, like many things in Tableau, with a little creativity you can get what you need, and there is a way where you can have your Sets anywhere you want.

If you’re not familiar with sets, then I suggest you start with Hot. Dirty. Sets. That was the title of a great session on sets by Russell Christopher and Michael Kravec at the Tableau Customer Conference in DC last month, the session video is up at http://tcc13.tableauconference.com/sessions. In their presentation Russell and Michael plumbed the depths of bad puns and went through a number of use cases. If you’re not familiar with Tableau version 8 sets, definitely watch the video. If you didn’t got to TCC, I suggest you watch this (vanilla) Tableau On-Demand training video on sets.

For this post I’ll attempt to avoid any more jokes about sets and show you how you can get jiggy with use sets from blended secondary data sources, and probably learn a bit more about data blending on the way.

In this tutorial I’ll be using the Superstore Sales and Coffee Chain data that ship with Tableau, with the Superstore Sales as the primary, Coffee Chain as the secondary. The Coffee Chain data is an Access database, and Tableau uses Microsoft JET for the driver for Access, Excel, and text files (this will change somewhat in 8.2). Microsoft JET has more limited functionality than other database connectors, so the first step is to extract that data into Tableau’s data engine. That will enable us to use the IN/OUT of sets.

Then in the Coffee Chain database we create a set for the Top N States by Sum of Sales:2013-10-10 23_50_20-Tableau - sets from secondary

And here’s a view of that Coffee Chain data, where there are only 20 states:2013-10-10 23_51_46-Tableau - sets from secondary

But when we set up a view based on Superstore Sales and switch over to the Coffee Chain data, any and all sets are taboo greyed out:2013-10-11 00_02_28-Tableau - sets from secondary

However, we can use sets in calculated fields. The In/Out of a Set returns a boolean value, and here’s a simple formula used for the In Top N CC States calculated field:

IIF([Top N CC States],"In","Out")

This is treated as a dimension in the CoffeeChain data, which can lead to all sorts of applications, including using as a filter, color, or in additional calculated fields.

Note that in production, I’d probably use 1 and 0 as values instead of “In” and “Out” and then use aliases (because integers are faster than strings), and to solve a filter issue, however for this demo I’ll stick with In and Out to make it easier to understand and explain the filter issue further down.

Set from Secondary as Filter

Here’s a starting view from Superstore Sales as our primary data source:

Screen Shot 2013-10-26 at 11.11.44 AM

Before putting the In Top N CC States on the Filter Shelf, though, in the Coffee Chain data click on State as a linking field:

Screen Shot 2013-10-26 at 10.52.50 AM

Important note: whatever dimension(s) you use for the Set must be the blending dimension(s), no more and no less, if they are not then your results will be mighty strange. I’m still trying to work out what’s happening there. If you really need some calculation that blends on a different set of dimensions, then use a duplicated data connection for that blend.

Now you can drag the In Top N CC States calculated dimension from the Coffee Chain data onto the Filters Shelf, and here’s the filter setting:

Screen Shot 2013-10-26 at 10.52.20 AM

And the view:2013-10-11 00_06_11-Tableau - sets from secondary

The worksheet is now only returning the 5 states from the primary and secondary data sources that meet the filter criteria from the set from the secondary data source. You’ve now used a Set from a secondary source!

Set from Secondary on Color

How about using the set calculation in the view? If we change the In Top CC States to All values, and put a copy of the pill on the Color Shelf then we see three different values: In, Out, and Null.

Why are there Nulls? This is one aspect of data blending that can be confusing. In the Superstore Sales data, there are 49 US states (48 contiguous states plus the District of Columbia). In the Coffee Chain data, there are 20 states. 5 of those are In the Top N CC States set (blue above), 15 of those are Out (light orange). That leaves 29 states in the Superstore Sales data have no corresponding rows in Coffee Chain. For the In Top Top N CC States set calculation, rather than assigning those 29 states to In or Out, Tableau assigns a value of Null to those states because they don’t have corresponding values, and in this case they get a dark orange color. This is the same behavior Tableau has for any linked dimension value from the secondary that doesn’t have corresponding values in the primary.

How can we help those Nulls come Out of the closet become part of the Out of the set?

This assignment of Null values is done in the primary data source, as there’s so way we can change it in the secondary (without doing some sort of padding in the secondary for all of those states). However, there is a way we can do that in the primary data source. Tableau does all the computation it can within each data source, then blends the data sources together, at which point things like calculations that refer to another data source are computed. We can create a calculation in the primary that refers to the set in the secondary, and test whether the set calculation is returning Null. Here’s the formula from Superstore Sales for the Primary In Top N CC States calculated field:

IFNULL(MIN(IIF([Sample - Coffee Chain (Access)].[Top N CC States],"In","Out")),"Out")

The inner IIF() is a row-level calculation that evaluates the In/Out of the Set, then that gets wrapped in MIN() because we’re working across the blend – Tableau requires us to aggregate all measures and dimensions used in calculated fields from other data sources. The IFNULL() then tests the result of the MIN(), and if there’s a Null from one of those 29 states that is in the primary but not the secondary, the IFNULL() Outs that one as well. Here’s a view with the calculation, where now everything is In or Out:2013-10-11 00_27_02-Tableau - sets from secondary

But you don’t have to be monogamous limit yourself to just one set. You can use combined sets from the secondary, for example in this view with CoffeeChain as the Primary I’m showing the top and bottom 5 states from the CoffeeChain data: 2013-10-11 00_35_02-Tableau - sets from secondary

Now to talk about complications and workarounds before getting into some advanced cases:

Discrete vs. Continuous Measures

It’s important to note that the Primary In Top N CC States set calculation is a measure, because it’s using an aggregate from the secondary data source. 2013-10-11 00_38_12-Tableau - sets from secondary

One effect is that Tableau won’t let us filter on discrete (blue) measures like the Primary In Top N CC States:

2014-01-30 17_59_48

My understanding is that this has to do with Tableau needing to know the domain (range of values) of measures to build the filter. (Here’s a Tableau Idea to support this, vote it up!) However, we can filter on continuous measures, so if you want to use that primary filter you can change the calc to return numbers instead of strings, like this calc called Primary In Top N CC States Continuous:

IFNULL(MIN(IIF([Sample - Coffee Chain (Access)].[Top N CC States],1,0)),0)

And this will work fine as a filter:

2014-01-30 18_07_01

Alternatively, you could use a table calc filter such as LOOKUP([Primary In Top N CC States],0) since discrete measures based on table calculations can be used as filters. I prefer the regular aggregate for performance reasons: Table calculation filters are applied after the data has been returned to Tableau, so that can lead to a lot of unnecessary traffic across the wire.

Using In/Out of Secondary Set in Primary Crosstab

In a simple view in Coffee Chain and our Top N set, we can quickly see the Sum of Sales for the In/Out of the set: 2013-10-11 00_46_08-Tableau - sets from secondary

We can even set that up from the Superstore as Primary, using the In Top N CC States set calculation from the Coffee Chain secondary, all we need to do is make sure that State is turned on as a blending field and do a little extra filtering to get rid of those pesky Null values:2013-10-11 00_51_13-Tableau - sets from secondary

 

Advanced Uses for Secondary Sets

You can mix and match Sets from Primary and Secondary sources, here are three examples:

Cohort Analysis

In this view,  we’re looking at trends for profits broken down by the performance reviews of our sales people, looking to see if there are any trends. The sales data comes from Superstore Sales, the performance reviews are coming from a secondary data source. The panes are created by the In/Out of the Top 40 Customers by Profit. The top pane shows the sales for the Top 40 customers, the bottom pane everyone else. The lines are colored by the In/Out of the Top 5 Salespeople:

Screen Shot 2014-01-30 at 6.31.38 PM

 

 

Combining Sets Across Data Sources

To create a combined set, we can use a calculated field that evaluates the sets from the different data sources, like this one that gets the intersection of the Top N States for Sales from both Superstore Sales and Coffee Chain, assuming the Superstore Sales is primary:

Screen Shot 2014-01-30 at 6.36.59 PM

 

And here it is used in a view, with the Top 5 States from each, only 3 states overlap:

Screen Shot 2014-01-30 at 6.37.32 PM

 

The Three Way

Why stop at only two sets from different data sources? In this view using Coffee Chain as primary, we’re coloring the states based on the In/Out of the Top N States by Coffee Chain Sales, the Top N States with highest # of Starbucks per capita (data from Statemaster.com), and Top N States by Superstore Sales. Three data sources, three sets:

Screen Shot 2014-01-30 at 6.45.24 PM

And, of course, you can build calculated fields as well, here’s one in Superstore:

Screen Shot 2014-01-30 at 6.46.44 PM

Screen Shot 2014-01-30 at 6.48.00 PM

 

Conclusion

Here’s a link for the Tableau Public workbook for Sets with your Secondary.

With a little creativity and a whole lot of jokes at the maturity level of the average American teenage boy, we can get more out of data blending and sets. If you’d like Tableau to have more support for secondary sets out of the box (and generally treat secondary data sources more like primary data sources), please vote for http://community.tableausoftware.com/ideas/2773.

Now, go forth and use sets in strange places any position you want in new ways!

Sets

Think of set filters as a combination of filters (a concatenation of dimensions). The easiest way to create them is to create a worksheet and select them, however Sets can be dynamically generated as well.

Sets with lots of members are slower than concatenated fields.

v8 sets can create a quick top N/bottom N:

http://community.tableausoftware.com/message/205502#205502

Related posts:

  • How to Have Sets with Your Secondary (Data Sources)
  • Sorting and Top N
  • Sorting and Top N

    Why Tableau sorts the way it does (Ross Bunker post):

    http://community.tableausoftware.com/message/182650#182650

    Nested Sort
    http://kb.tableausoftware.com/articles/knowledgebase/nestedsorting
    http://kb.tableausoftware.com/articles/knowledgebase/sorting-concepts-related-quick-table-calculations

    Dynamic sort:
    http://www.clearlyandsimply.com/clearly_and_simply/2011/11/dynamic-sorting-with-tableau.html

    find top N within category (using a set):
    http://kb.tableausoftware.com/articles/knowledgebase/finding-top-n-within-category
    can also use Index() instead of a Running Total of # of Records
    this also shows how to do a nested sort

    Top N within a Category:
    http://www.tableausoftware.com/support/knowledge-base/computed-sets

    Just show top N lines, using filter within IF statement within Top N filter:
    http://community.tableausoftware.com/thread/119057

    Find Top N based on most recent results:
    http://community.tableausoftware.com/thread/117576?start=15&tstart=0

    Show Top N, then put everyone else in an “other” group

    Long thread on how to do this:
    http://community.tableausoftware.com/thread/109093

    another thread on this:
    http://community.tableausoftware.com/thread/114541

    easy top N and bottom N via v8 sets:

    http://community.tableausoftware.com/message/205502#205502

     

    Top N filter and order of filter application
    http://community.tableausoftware.com/thread/115792

    some complex sorting within table calcs
    http://community.tableausoftware.com/thread/109013

    Sorting or ranking within a dimension, with some funky stuff with groups and colors
    http://community.tableausoftware.com/message/172626#172626

    Sorting an “Other” dimension member at the end of a list, post by Andy Kriebel w/useful comment from Joe Mako at the bottom:
    http://vizwiz.blogspot.com/2013/09/othersort.html

    Sorting a view by a table calc

    – create the table calc
    – create a calculation that uses the table calc to generate something that Tableau’s default sort will work just fine with, e.g. low to high numbers
    – put the calculation on the Rows shelf
    – set the calculation to Discrete
    – drag the blue pill to the leftmost position on the Rows shelf
    – uncheck Show Header for that pill

    Example from a forum post I created:

    http://community.tableausoftware.com/message/175836#175836

    Example from Joe Mako, for making a fake color legend that sorts based on a measure:
    http://vimeo.com/20815794

    Standard Rank, Standard Competition Rank

    James Baker post:
    http://community.tableausoftware.com/thread/108967

    Show top results with >N% of total, everything else as other
    http://public.tableausoftware.com/views/AcceleratedStackedPercentofTotal/AcceleratedStackedofTotal

    Top 25%/bottom 25%
    http://community.tableausoftware.com/message/183972#183972
    (Joe’s workbook is another example of “partitioning” by table calculations)\

    Another partitioning by table calc workbook:
    http://community.tableausoftware.com/thread/118445


    sort by more than one measure - Added October 6, 2014

    Two options - sort by RANK, or by using one measure that is offset by a number of decimal places plus the other measure http://community.tableausoftware.com/ideas/3261#comment-9526


    Related posts:

  • Top 10 Table Calculations - The Next N, Where N >= 15
  • Upcoming Presentations
  • Bitten by the Sort Fly