Tag Archives: filters

Row Level Security Using Tableau 10.0 Cross Data Source Filters by Jamieson Christian

Jonathan here. Serendipity is a wonderful thing! I was briefly on the Tableau community forums last week and found this thread where Jamieson Christian described a new method for user- row-level security in Tableau that I hadn’t seen before, and it’s so awesomely cool I asked him if he could blog about it, and he accepted my invitation! I’ve already implemented this method at a client and it brought a dashboard from unacceptable refresh times to nearly instantaneous. So read on for Jamieson’s description.

Row level security often presents challenges, if your security data is not in the same data source as your main data. In such cases, solutions tend to leverage one of four techniques:

  1. Data prep outside of Tableau – often involves cross products that can make data sets unacceptably large.
  2. Passing parameter-based filter through Tableau’s Javascript API – requires using a portal that is not always feasible for organizations, also won’t work with Tableau Mobile.
  3. Data blending — just about the only in-Tableau option prior to Tableau 10.0.
  4. Cross data source joins — available in Tableau as of Tableau 10.0.

This post introduces a 5th option using a cross data source filter. see below for how to set it up!

Continue reading

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"
ELSE
    "Arrr!"
END

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

Complications of using ATTR()

One additional note that I discovered after this was posted, this text is copied from How to Have Sets with Your Secondary (9.2 Style) via Aggregated Booleans.

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.

Conclusion

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).

 

Keeping a Value in Totals Whilst Excluding from Quick Filter List

Over at Peter Gilk’s Paint by Numbers blog there was a question on this post on filtering while retaining results. Here’s the what Jeremy asked:

May I ask if it would be possible to get a detailed explanation of applying this principle to a different type of data?

For example, I would like to see the US Sales totals, and have the ability to filter it to a US state without the ability to select a US territory (Guam, Puerto Rico, etc), but to have the US territory sales remain in the US national totals. How could I do this?

In this short post I cover two different techniques how to do this using a self-data blend and LOD expressions, respectively.

Continue reading

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!

Filtering and Hiding

Joe Mako’s Order of filtering post:

http://www.tableausoftware.com/support/forum/topic/question-how-create-product-velocity-calculation#comment-39603

A long post on global filters, action filters, etc.
http://community.tableausoftware.com/thread/109284

Global filter across data sources (parameter with calc field to test the parameter)
http://vizwiz.blogspot.com/2012/06/create-global-filter-in-tableau-across.html

Complex sorts in table calcs
http://community.tableausoftware.com/thread/109013

use as filter is faster than quick filters

Use as filter can work on any clickable element in the view

  • Edit actions to change what they use
  • see the TCC11 – JediTricks video for some funky global filtering

(use in nursing dashboard for per-unit stuff?)

Type-in on a quick filter is used as a wildcard, to change this use a type-in parameter and then a calc field with:

[ID] == [ID Parameter]

That will return true for the one row of interest and false for everything else. Just put that on the filter shelf and select just “true” (you’ll need to set the parameter to a value that exists to have the “true” option available).

Can’t drag a filter

If a pill can’t be dragged onto the filter shelf, try converting it into a continuous.

Lookup0 to make filters work locally after all db calculations are done. Handy to filter in %ofTotal calc. #tableaujedi #TCC11
by mohanganeesh at 10/18/11 2:38 PM

Hide happens after the data calculations. Filter happens before. Very handy to use hide in % of total calc. #tableaujedi #TCC11

Here’s a hiding link using the LOOKUP() trick:
http://community.tableausoftware.com/message/172343
File is Hide via Filter on Table Calc.twbx.zip.

Create a field called Date Filter w/value LOOKUP(ATTR([date]),0). Tableau will treat it as a date for the filter functions, but still have all data available to the calculations.

Hide (filter) rows if first column is empty
http://community.tableausoftware.com/thread/116249

Filtering first year out of YoY calculations:
http://kb.tableausoftware.com/articles/knowledgebase/filter-first-year

Filtering YoY to same month/day as current date:
http://www.freakalytics.com/2012/06/05/balancing-analysis-of-multiple-years-by-filtering-through-the-same-monthday-as-today/

Grabbing certain prior dates out of the data with user-selectable “filter”:

one way to filter for data within specific dates:
http://community.tableausoftware.com/thread/116985

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

IF [scanDate] >=[StartDate] AND [scanDate] <= [EndDate] THEN "Keep" ELSE "Remove" END

Restrict filter options in a Quick Filter based on another Filter

Mark Jackson demonstrates a couple of options in http://community.tableausoftware.com/message/210455#210455

Can use Action Filter

Find first month in a set of data

My response to http://community.tableausoftware.com/message/177996

which is based on: http://community.tableausoftware.com/message/168597#168597
workbook is running sum of count distinct.twbx

IF TOTAL(MIN([Month of Install]))=ATTR([Month of Install]) THEN 1 ELSE 0 END

Calculation uses TOTAL which doesn’t care about sorting/addressing, so it’s a lot more bulletproof.

Another one for last date:

As an alternative to LAST()==0 from Ross Bunker in http://community.tableausoftware.com/message/178251#178251

ATTR([Order Date]) == WINDOW_MAX(ATTR([Order Date]))

Hiding certain items/dimensions in a % of total

For hiding certain items/dimensions in a % of total – e.g. PrimeCare/Non-PrimeCare/Null, just show % of total that is PrimeCare

1.     Create a calculated field called “Hide [Dimension]” with calc:

LOOKUP(MIN([dimension]),0)

results in “find my value” when the [dimension] is in the level of detail 2. Add that calc field to the level of detail 3. Create a Quick Filter on the calc field

Enjoy!

from TCC11 – JediTricks video

Using a set:
http://community.tableausoftware.com/thread/109355
and attached workbook (upgraded so it will work with current data)

Using a simple IF statement:

A way to “filter” dates by using LOOKUP()
http://community.tableausoftware.com/thread/115403
full_running.twbx workbook, simpler solution worksheet

Create a calculated field that is LOOKUP([Date],0) and make it continuous. Put it on the filter shelf for Range of Dates and leave it at that. When the user changes the slider, the range will change.

Hide NULL values

http://onlinehelp.tableausoftware.com/v5.2/online/Output/wwhelp/wwhimpl/common/html/wwhelp.htm?context=Tableau_Online_Help&file=Tableau%2520Online%2520Help-29-24.html

Dashboards and filters

http://vizwiz.blogspot.com/2011/08/tableau-tip-local-vs-global-filters-on.html

http://reports4u.co.uk/2011/06/10/tableau-dashboard-level-filter/

Robert Morton with a bit on when filters might not be able to properly compute the domain: http://community.tableausoftware.com/message/215056#215056

Data source filters are not applied directly to the data that is extracted, instead are added to each query. Post from Robert Morton: http://community.tableausoftware.com/message/227815

[loop category=”wikicontent” tag=”filtering,filters,filter”]
[field title] – Added [field date]
[content]
[/loop]

Related posts:

    [loop tag=”filter,filtering,filters,hide,hiding,LOOKUP()” exclude=”this” relation=”and” compare=”not” taxonomy=”category” value=”wikicontent”]

  • [field title-link][field thumbnail-link]

[/loop]

A New Compact (Mostly) Filter Layout for Showing All the Options

I’m preparing my Tableau Customer Conference 2013 session and there is way too much material to present in the time available, so I’m killing my darlings with a couple of posts in the next few weeks.

I had a situation where my users needed to select from a basket of hospital quality measures on a dashboard, and since some of the measures were new I wanted the filter to show all of the available options, and based on the dashboard layout there wasn’t really space on either side of the view for a vertically oriented filter.

What I really wanted was to show all the options at the top of the dashboard, I ended up getting a little creative and coming up with a new  filter layout. Read on to find out how!
Continue reading

Creating a Dynamic “Parameter” with a Tableau Data Blend

As of June 2015, Tableau is actively looking at different aspects of the (complicated) subject of dynamic parameters. See Dynamic Parameters: The Results are In for an overview, and Dynamic Parameters Update for continuing updates.

Currently (July 2013), the #1 most-voted-for Idea for Tableau is Dynamic Parameters. Here, I’ll show you a technique for using Tableau data blending to create a dynamic, data-driven “parameter”. We’re going to use a loosely coupled secondary data source to get the information associated with the “parameter” and return that information to the primary data source, where it can be used in further calculations. Some examples of where this can be useful:

  • Choose one value to build a comparison to other values, such as finding the distance from a chosen origin city to a set of destination cities, or a market basket-type analysis where we want to compare one against others.
  • Set the limits and input data to an algorithm that is then used to create other results, for example to get a starting set of data to use to build a projection, such as an executive retirement forecast model.

Read on for a description of the technique and demos of all three options!

Continue reading