Tag Archives: percentiles

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
  • Partitioning by Table Calculations

    Here’s a issue on the Tableau forums that shows up at least once per month or so: someone has a data set that they want to compute a measure over, and the measure requires table calculations. Then they want to use the results of those calculations as a discrete value for partitioning other calculations. For example, given a set of student grades, compute each student’s percentile, then show the average of each percentile in the same view. Or a similar idea, only the percentile is based on sum of sales, and you want to show the top 10% and bottom 10% of sales. Continue reading