Tag Archives: general

Analyses

This section covers how to generate different types of analyses in Tableau. For example, how to do your own projections and forecasts (a lot of that material comes from Tableau v7 and earlier), some cohort analysis, etc.

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

Projections

Forecasting, Run Rate and Projected Visits:

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

projections – iterative over months, messy
http://community.tableausoftware.com/message/178156
file is: Projection Sample jtd edit.twbx

projections – easy linear trend
http://community.tableausoftware.com/thread/117629
file is: Forecast jtd edit.twbx

Another projections thread:
http://community.tableausoftware.com/thread/111437

 

BYO Optimal Trend Line using table calcs

http://community.tableausoftware.com/message/180906#180906
workbook is Optimal Trend Line.twbx

Duplicate data to do funky action filter where the filtered data still shows difference from current to previous
http://community.tableausoftware.com/message/174252#174252

Comparing dimensions

Also check out Sets, lots of possibilities there.

Comparing different dimensions (surveys, etc.)
http://kb.tableausoftware.com/articles/knowledgebase/calculating-percent-difference-across-dimensions-60

comparing items via table calc across columns:
http://kb.tableausoftware.com/articles/knowledgebase/comparing-items

Each against every other
http://drawingwithnumbers.artisart.org/comparing-each-against-each-other-the-no-sql-cross-product/

 

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

http://community.tableausoftware.com/message/171706#171706\

Compare one against the rest

http://kb.tableausoftware.com/articles/knowledgebase/comparing-items

http://interworks.co.uk/andy-cotgreave/us-v-the-rest/

One vs. all other

Correlation Matrix by Bora Beran

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

http://community.tableausoftware.com/thread/112972
workbook is date_level_over_year_ago_or_prev.twbx

Benford’s Rule

http://community.tableausoftware.com/thread/118570
http://en.wikipedia.org/wiki/Benfords_law

Create monthly calendar
http://vizwiz.blogspot.com/2012/05/creating-interactive-monthly-calendar.html
https://www.interworks.com/blogs/dwyers/2012/05/22/creating-calendar-views-tableau

Quantiles

Big post on percentiles with Tableau:
http://www.equinox.co.nz/blog/Lists/Posts/Post.aspx?ID=160

From http://community.tableausoftware.com/thread/113243 by Richard Leeke:

[A] is the measure

is the quantile, e.g. .25 for 25th, 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 (

== 0) THEN ATTR([A]) // p = 1 => maximum, so lookup the last row ELSEIF (

== 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

* 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

* 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 0th or 100th percentile:

PREVIOUS_VALUE(   
    IF (FIRST()==0) THEN   
        IF (

== 0) THEN ATTR([A]) ELSEIF (

== 1) THEN LOOKUP(ATTR([A]), LAST()) ELSE WINDOW_MIN( IF

* TOTAL(SUM([Number of Records])) = RUNNING_SUM(SUM([Number of Records])) THEN (ATTR([A]) + LOOKUP(ATTR([A]), 1)) / 2 ELSEIF

* 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 0th and 100th percentile:

PREVIOUS_VALUE(   
    IF (FIRST()==0) THEN   
        IF (

== 0) THEN ATTR([A]) ELSEIF (

== 1) THEN LOOKUP(ATTR([A]), LAST()) ELSE WINDOW_MIN( IF

* TOTAL(SUM([Number of Records])) = RUNNING_SUM(SUM([Number of Records])) THEN (ATTR([A]) + LOOKUP(ATTR([A]), 1)) / 2 ELSEIF

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

Find people who also belong to dimension X
http://community.tableausoftware.com/thread/114620

Concatenating a field from multiple rows into one field using PREVIOUS_VALUE

Richard Leeke version:
http://community.tableausoftware.com/message/180164#180164

Alex Kerin version:
http://community.tableausoftware.com/thread/118259

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

Dynamic anchor date with a  relative date filter
http://voyrapido.tumblr.com/post/22763424744/creating-a-dynamic-anchor-date-with-a-relative-date

Stephen Few’s bandlines in Tableau
http://community.tableausoftware.com/message/198511

For surveys, see Chart Types.


weighted median (old post) - Added December 7, 2014

Probably easier ways to do this as of 2014, this was good at the time: http://community.tableausoftware.com/thread/119081



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



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

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



Net present value calculation - Added October 5, 2014

Built by Wilson Po
http://community.tableausoftware.com/docs/DOC-5525


Related posts:

  • Counting from Nothing - A Double Remix (or, Partitioning via Table Calculations v2)
  • Test page
  • Table Calculations
  • Creating a Dynamic "Parameter" with a Tableau Data Blend
  • Partitioning by Table Calculations
  • Tricks + Miscellaneous Techniques

    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

    1. Create a calculated field with just a space
    2. Put that field on the Columns shelf and on the Text shelf
    3. Uncheck Show Header
    4. 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

    Dynamic Top X and Bottom X labels

    http://vizwiz.blogspot.com/2011/11/tableau-tip-adding-dynamic-top-x-labels.html

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

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

    Custom title for second axis in dual axis chart
    http://community.tableausoftware.com/thread/119013

    Anonymizing data
    http://community.tableausoftware.com/thread/118843

    Counting Overlapping Marks

    http://www.datadrivenconsulting.com/2011/04/counting-overlapping-marks-in-tableau/

    Tooltips

    http://www.thedatastudio.co.uk/tag/tooltips
    http://www.thedatastudio.co.uk/blog/the-data-studio-blog/andy-cotgreave/terrific-tooltip-tricks

    Fake fill down

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

    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/


    Create your own cartograms - Added July 8, 2015

    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:
    http://vizpainter.com/tableau-tip-tuesday-the-first-question-to-ask/



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

    By Olivier Catherin, drawing on Jeffrey Shaffer's work
    http://community.tableausoftware.com/thread/154623



    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

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


    Related posts:

  • Aggregations