Tricks + Miscellaneous Techniques

These are either single tips or chunks of techniques that don’t have their own page yet.

Be sure to check out the following sources of material on the Tableau Community:

Fixing axis size

Use an invisible reference line, either based on a constant or a calculated field. Tableau will make sure the axis range is big enough.

Show the same day last year in a view

Get max value of [Date] no matter sort order:

`attr([Date])=window_max(attr([Date]))`

Finding lowest values of a top N:

Hide the Abc column

1. Create a calculated field with just a space
2. Put that field on the Columns shelf and on the Text shelf
4. Drag the column left to shrink it

Showing most recent data

Doing a count of Distinct values via table calc (w/out using extract)

`PREVIOUS_VALUE(WINDOW_SUM(IIF(ATTR(A)==ZN(LOOKUP(ATTR(A), -1)), 0, 1), 0, IIF(FIRST()==0, LAST(), 0)))`

Set Compute Using to A

Time and Date:

One calc to generate hh:mm from a time:

`str(int([Time])) + ":" + str(([Time]%1)*60)`

Also, from Dan Cory in this thread http://community.tableausoftware.com/message/224840

` (FLOAT([Time DateTime])+2)*24*60*60`

This will be much faster than using DATEPART but will only work for some databases. The FLOAT converts the Date & Time field to a number, using the rules of the database. The +2 is because times are stored as dates on Excel’s zero day, but Jet’s zero day is two days later. The *24*60*60 is because you get a result in days and it has to be converted to seconds.

Rolling calculations:

http://www.tableausoftware.com/support/knowledge-base/rolling-calculation

Moving average of running average:

Queue length/census:

Use different breaks on time, e.g. ½ hour, 6am to 6pm etc. – do a manual sort on the times, idea by Alex Kerin:
http://www.screencast.com/t/Lws7tcKC

Another Alex post on padding for hours
Richard’s approach was to create a day/hour/minutes table at the level of granularity needed for reporting, then join to that with Custom SQL like this (for the hours table). I think of this as “generate a row for every event/time bucket” combination. So if you want to report down to the minute, there’s a row for every event+minute that the event occurs in.
http://community.tableausoftware.com/message/188617

Getting the total for a count(distinct) where we want to find the # of unique kinds of X across Y dimension
http://kb.tableausoftware.com/articles/knowledgebase/getting-total-count-distinct

create a new calculated field Y+X (it’s a string) and then do a COUNTD() on that.

Making grand totals do anything you want (via table calcs)

This section is basically deprecated by some posts I wrote, here’s the first one: Customizing Grand Totals – Part 1.

Workbook is: Top as Percent of Total jtd edit

Variation on this since grand totals run at a higher level of aggregation, when using table calcs it is a visual total
http://community.tableausoftware.com/message/180911#180911

Joe Mako’s workbook is control of Grand Total.twbx

Another Joe Mako workbook on doing this from June 2012:

A workbook where I did both the hack and “put the Grand Total in a separate worksheet on a dashboard” technique:

And here’s an Idea to have the totals show what is visually displayed in the view: http://community.tableausoftware.com/ideas/1232

Duplicating a dimension to preserve level of detail in table calcs

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

workbook is blanding (2) jm edit.twbx

Bar chart and KPI on same row (without dashboard)

Use dual axis

files: utilization_bar_chart_and_kpi_dc.twbx, and utilization_example_dashboard_packaged_rl.twbx

Make axis name dynamic, i.e. based on what’s chosen in a parameter

Custom title for second axis in dual axis chart

Anonymizing data

Fake fill down

Fill Down calc:

`if isnull(attr([Amt])) then previous_value(attr([Amt])) else attr([Amt]) end`

then can use LOOKUP([Fill Down],-1)

Colors

from TCC11 – JediTricks video

Custom Colors

Tableau blue:

31/119/180 in decimal

use parameter to Partition day into even chunks – Richard Leeke post
http://community.tableausoftware.com/message/183317#183317

Use URL actions to create emails
http://kb.tableausoftware.com/articles/knowledgebase/using-url-action-to-create-email

Adding an All parameter option in Tableau

Using RAWSQL

Page Shelf

From Think Data Thursday #4, 9/13/12

To use the Page Shelf to create lines, one suggestion is to use circle marks, the Path Shelf, and then history to make the lines.

Size shelf
From Shawn Wallwork – Can get more choice about sizes by creating dummy discrete elements/rows in the view

putting commas back in numbers for STR([number]):

lots of good stuff on custom number formats http://www.clearlyandsimply.com/clearly_and_simply/2011/04/tableau-quick-tip-2-custom-number-formats.html

Example:

Unit PI plans are on X:\QM Data\MS3\Unit PI Plan\MS 3 Quality PI Plan Q4 CY11 .docx
Can link to file (opens in Word) using file:///X:/QM%20Data/etc. where the string is URL encoded

Trend lines – post by Catherine Rivier
http://community.tableausoftware.com/message/196021#196021

Stripping the date to get just the time – from Alex Kerin

`[date]-int([date])`

Using a parameter for a dynamic in-line caption – users can enter their own annotation

http://www.alansmitheepresents.org/2013/01/dynamic-in-line-captions.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+AlanSmitheePresents+%28Alan+Smithee+Presents%29

Using a reference line or annotation to create arbitrary borders and lines (posts by Joe and I)
http://community.tableausoftware.com/message/215099#215099
http://community.tableausoftware.com/message/215100#215100

Showing/hiding annotations by using a duplicated calculation, one has the annotation the other has the point.
http://vizpainter.com/89/

Making CASE statements

WhenMaker from DecisionViz
http://www.decisionviz.com/#!whenmaker/ct1k

using two measures to color the same mark (set a color, then overlay marks w/transparency)
https://www.interworks.com/blogs/wjones/2013/09/18/create-dual-color-axis-tableau

Pinning a bar to the top/middle/bottom of the pane to leave room for a reference line
http://steineranalytics.com/2013/10/29/the-one-trick-to-control-tableau-viz-display/

http://www.clearlyandsimply.com/clearly_and_simply/2015/06/create-your-own-cartograms-in-tableau.html

Typography tweaks - Added March 8, 2015

From Richard Rouse @bibleviz
https://www.interworks.com/blogs/rrouse/2015/03/05/my-favorite-tableau-typography-tweaks

What to do first: understanding granularity of data - Added January 15, 2015

From Joshua Milligan:

decision trees, Sankeys, flow diagrams - Added January 13, 2015

By Olivier Catherin, drawing on Jeffrey Shaffer's work

testing Tableau for Mobile on a non-mobile browser - Added January 13, 2015

http://community.tableausoftware.com/docs/DOC-5346?et=watches.email.document

Tickers on Server - Added December 10, 2014

From Russell Christopher:
http://russellchristopher.me/youdidwhat/

CASE statement generator for groups - Added December 7, 2014

Opens a .twb’s XML to convert groups into a CASE statement so they can be used in calculated fields. By Alex Kerin from 2012: http://community.tableausoftware.com/message/193036

early thread on jittering by Alan Eldridge - Added October 6, 2014

• Aggregations
• Aggregations

Using ATTR()

Nice description of ATTR() here:

Another description of ATTR() and Level of Detail:

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

Evaluation to see if ATTR() is returning *:
IF NOT ISNULL(ATTR([Market])=”*”) THEN “Here” END
How this calc works, from Joe: “…if the result of comparing the ATTR() to a string is NULL, than I know the ATTR() function is displaying a “*”.”

Note – if the field is not a string datatype, then use:
IF NOT ISNULL(STR(ATTR([otherfield]))=”*”) THEN “Here” END

Using ATTR() to determine current level in an expanded/collapsed hierarchy

Can also do a check like this to see if ATTR() in Primary matches ATTR() in Secondary:
IF ATTR([Market]) = ATTR([Sample – CC Extract (Access)].[Market]) THEN “Match” ELSE “No Match” END

Including Group Results as Reference Lines in Individual Results

1. Do things the way I initially did – generate the results for the population in a worksheet, export those results, and then reimport them into the data set either by joins or blends.
2. Do a whole bunch of table calculations, which can get really slow.
3. Use data blends and do a self-join back to the data source and then create calculated fields to get the population results. The only challenge here is that whatever Filters are used against the original data have to be duplicated inside the calculation. Whenever the underlying data is updated, every data source will need to be refreshed. (This last point can go away in Tableau 7).
4. A variation on #3 when using extracts, instead of re-linking back to the original data source, when adding the 2nd data source point to the TDE file instead. This means that whenever the extract is updated, all the results will be updated.
5. RAWSQLAGG or Custom SQL is another possibility.

For example, in AHRQ outpatient survey, to do the office type & composite reference line, duplicate the data source, and join back to the original on office type & composite.
Another example, from Blood Transfusion, where the display is an aggregate and the reference lines need to be based on the original data.
• Distribution of Hgb view has Date and Hgb on Rows shelf, # Transfusions on columns
• Duplicate data source or add the TDE
• Add the necessary measures, probably don’t need table calcs since the blend is a left-join and will return all rows

Table that shows MIN MAX and AVG for several measures - Added January 14, 2015

Nice discussion on text tables and profiling multiple measures: http://community.tableausoftware.com/message/319156#319156

Related posts:

• How to Have Sets with Your Secondary (Data Sources)
• Table Calculations
• Tricks + Miscellaneous Techniques
• Preparing Data
• Partitioning by Table Calculations
• Unexpected Results: Rounding
• Customizing Grand Totals - Part 2
• Customizing Grand Totals - Part 1