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:

Hide-1

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.

Hide-2

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:

Hide-3

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:

Hide-4

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:

Hide-5

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

Advertisements

One comment on “Tinkering with Filtering vs. Hiding

  1. jack says:

    Great tip, thanks for the help! I found this really helpful for hiding blank rows when generating totals using table calcs – as per https://community.tableau.com/thread/201526

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s