Quantiles

[A] is the measure
[p] is the quantile, e.g. .25 for 25^th, etc.
Set compute using to [A], may need to use and advanced compute to order along the desired field.

Return only one value:

// Only calculate for first row of the partition, otherwise return NULL IF (FIRST()==0) THEN // p = 0 => minimum, and we are on the first row, so we just want the current value IF ([p] == 0) THEN ATTR([A]) // p = 1 => maximum, so lookup the last row ELSEIF ([p] == 1) THEN LOOKUP(ATTR([A]), LAST()) // otherwise select the appropriate value ELSE // The WINDOW_MIN() finds the lowest value at or beyond the required quantile. WINDOW_MIN( // required quantile is at the mid-point between two values, so take the average IF [p] * TOTAL(SUM([Number of Records])) = RUNNING_SUM(SUM([Number of Records])) THEN (ATTR([A]) + LOOKUP(ATTR([A]), 1)) / 2 // row is beyond required quantile ELSEIF [p] * TOTAL(SUM([Number of Records])) < RUNNING_SUM(SUM([Number of Records])) THEN ATTR([A]) ELSE NULL END, 0, IIF(FIRST()==0, LAST(), 0) ) END

Return same value for all rows, not including 0^th or 100^th percentile:

PREVIOUS_VALUE( IF (FIRST()==0) THEN IF ([p] == 0) THEN ATTR([A]) ELSEIF ([p] == 1) THEN LOOKUP(ATTR([A]), LAST()) ELSE WINDOW_MIN( IF [p] * TOTAL(SUM([Number of Records])) = RUNNING_SUM(SUM([Number of Records])) THEN (ATTR([A]) + LOOKUP(ATTR([A]), 1)) / 2 ELSEIF [p] * TOTAL(SUM([Number of Records])) < RUNNING_SUM(SUM([Number of Records])) THEN ATTR([A]) ELSE NULL END, 0, IIF(FIRST()==0, LAST(), 0) ) END END )

Return same value for all rows, including 0^th and 100^th percentile:

Concatenating a field from multiple rows into one field using PREVIOUS_VALUE

find difference between dates, counting weekdays, counting work days
http://community.tableausoftware.com/thread/118000
http://community.tableausoftware.com/thread/104481
Also having a separate table that identifies working days/holidays and then joining to that

For surveys, see Chart Types.

[loop category=”wikicontent” tag=”analyses,forecasting,projection,percentiles,rank,cohort-analysis”]
[field title] – Added [field date]
[content]
[/loop]

[loop tag=”analyses,forecasting,projection,percentiles,rank,cohort-analysis” exclude=”this” relation=”and” compare=”not” taxonomy=”category” value=”wikicontent”]

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

[/loop]

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:

Get last value in dataset

http://community.tableausoftware.com/thread/116062

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
http://community.tableausoftware.com/thread/116944

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

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

Finding lowest values of a top N:
http://community.tableausoftware.com/thread/116882

Hide the Abc column

Create a calculated field with just a space
Put that field on the Columns shelf and on the Text shelf
Uncheck Show Header
Drag the column left to shrink it

Showing most recent data
http://community.tableausoftware.com/thread/116849

Doing a count of Distinct values via table calc (w/out using extract)
http://community.tableausoftware.com/thread/109935

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:
http://community.tableausoftware.com/thread/116305

Queue length/census:
http://community.tableausoftware.com/thread/107815 – original thread w/Richard Leeke

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
http://community.tableausoftware.com/thread/117903
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.

http://community.tableausoftware.com/thread/116854
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:
http://community.tableausoftware.com/thread/118467

And Dimitri’s edit: http://community.tableausoftware.com/thread/118705

A workbook where I did both the hack and “put the Grand Total in a separate worksheet on a dashboard” technique:
http://community.tableausoftware.com/thread/118338

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

Putting Grand Totals as left-hand column or on top (as a separate header via custom SQL)

http://community.tableausoftware.com/message/180921#180921
http://community.tableausoftware.com/message/178687#178687

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

http://community.tableausoftware.com/thread/111652
files: utilization_bar_chart_and_kpi_dc.twbx, and utilization_example_dashboard_packaged_rl.twbx

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

http://vis4.net/blog/posts/avoid-equidistant-hsv-colors/
http://colorbrewer2.com/ – site for picking colors
http://www.personal.psu.edu/cab38/ColorBrewer/ColorBrewer_intro.html

Tableau blue:

31/119/180 in decimal

Lots on statistics
http://vizcandy.blogspot.com/2012/06/some-basic-statistics-with-tableau.html
http://www.alansmitheepresents.org/2012/07/statistics.html

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
http://bensullins.com/how-to-add-an-all-parameter-option-in-tableau/

Using RAWSQL

http://community.tableausoftware.com/thread/119290

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]):
nice function here: http://community.tableausoftware.com/thread/109230

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

using the path shelf
http://kb.tableausoftware.com/articles/knowledgebase/using-path-shelf-pattern-analysis

Linking to files:

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/

[loop category=”wikicontent” tag=”tricks,techniques,misc,reference-lines,ref-lines,captions,general”]
[field title] – Added [field date]
[content]
[/loop]

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

[/loop]

Drawing with Numbers

Thoughts on data visualization and Tableau

Tag Archives: general

Analyses

Projections

Forecasting, Run Rate and Projected Visits:

BYO Optimal Trend Line using table calcs

Comparing dimensions

Find complementary products- if customer purchased X, see what else the purchased

Compare one against the rest

compare given period (quarter, month, year, etc.) to the same period some time ago (year or previous)

Benford’s Rule

Quantiles

Concatenating a field from multiple rows into one field using PREVIOUS_VALUE

Related posts:

Tricks + Miscellaneous Techniques

Tricks + Miscellaneous Techniques

Get last value in dataset

Fixing axis size

Rolling calculations:

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

Putting Grand Totals as left-hand column or on top (as a separate header via custom SQL)

Duplicating a dimension to preserve level of detail in table calcs

Bar chart and KPI on same row (without dashboard)

Dynamic Top X and Bottom X labels

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

Counting Overlapping Marks

Tooltips

Fake fill down

Colors

Using RAWSQL

Page Shelf

Related posts: