11/19/2014

Constructing a Dynamic Sankey Diagram product of Polygons in Tableau (Visitor Put up)

Written by: Olivier Catherin

As I used to be engaged on constructing a Sankey Diagram with Tableau, I discovered the superb articles by Jeffrey Shaffer from Information + Science for growing a strategy to construct a Sankey in Tableau.

Beginning with Jeff’s technique, I needed so as to add some interactivity on my diagram and decrease the information preparation. I simply needed to have the ability to construct my viz utilizing the mannequin offered by Jeff and a dataset with Step 1, Step 2 and Step 3 together with 1 indicator (dimension) and use dates to carry out filtering. I did not wish to use SQL or an Excel Macro to arrange the information.

I additionally had an ID subject, however I did not wish to use it.

I managed to construct my viz utilizing Jeff’s technique however confronted a difficulty relating to the efficiency of the viz. Exhibiting a number of 1000’s calculated strains + colours in the identical viz takes some sources, so I needed to simplify my viz. As a result of I may simply get all of the strains describing every stream on the diagram, I may outline the higher line and the decrease line of every stream. And if I may do this, I may hyperlink these strains to construct a polygon. I might then have far much less strains to calculate and present on my display. So right here is the problem.

Step 0: Learn Jeff’s Article

http://www.dataplusscience.com/SankeyinTableau82.html

http://www.dataplusscience.com/RecreationinTableau2.html

Step 1: Making ready the mannequin in Excel.

Please first obtain the Excel mannequin file from Data + Science.

Within the Excel sheet with the mannequin information, we are going to double the entire rows and add 2 columns:

A « path » column: order wherein we are going to hyperlink the factors

A « Min or Max » column: we are going to tag the curve whether or not it’s the min line or the max line.

**NOTE – the order of the trail must be ascending when Min and descending when Max.**

As soon as it’s performed, simply add the « Hyperlink » column within the dataset.

Step 2: Connect with the information (with Tableau 8.2).

I work on a Mac. Connect with the excel file containing the 2 sheets (information and mannequin). Let Tableau robotically construct the interior be a part of and go to work in Tableau!

Step 3: Construct the Viz.

We’ll first construct the dynamic aggregated steps utilizing parameters and units. Principally, we wish to present Prime N Step 1 sorted by the SUM of Measurement:

Create a « Prime N Step 1 » parameter: it must be an Integer with a min and a max.

Create a « Prime Step 1 » set:

Proper-click on « Step 1 » capsule.

Create a set.

Type the set utilizing Prime-By Discipline-Prime-Prime N Step 1(parameter)-Measurement(indicator)-Sum

Create an « Agg Step 1 » dimension:

Formulation:

IF [Top Step 1] (ie. the set) THEN

[Step 1]

ELSE

‘x Different’

END

I take advantage of the x to simply type the flows alphabetically.

Repeat the operation with Step 2 as a way to construct a brand new dimension known as « Agg Step 2 ».

These operations will allow us to decide on the variety of « nodes » to show within the bar charts and in addition to generate the positions of the flows within the Sankey.

Step 4: Construct the « Sigmoid » calculated subject.

Calculated Discipline Title: **Sigmoid Operate**

Formulation: **1/(1+EXP(1)^-[t])**

Step 5: Construct the « Circulation Measurement » calculated subject.

Right here is the place it will get fascinating. We wish to construct flows that may begin in a sure order and finish divided into a number of strains and in one other order. We’ll use a easy desk calculation for this.

The « Circulation Measurement » definition might be: SUM([Size])/TOTAL(SUM([Size]))

Calculated Discipline Title: **Circulation Measurement**

Formulation: **SUM([Size])/TOTAL(SUM([Size]))**

We then acquire the relative dimension of the stream in contrast the full sum of the indicator (in proportion). It will allow us to construct a steady show of the viz.

Click on on « Default Desk Calculation » on the highest proper of the Calculated Discipline window as proven under.

In « Compute utilizing » select « Superior. » Right here you’ll be able to select the way you wish to handle the partitioning. Select « Agg Step 1 » and « Agg Step 2 » on this order. Additionally, select to type the outcomes by Fields utilizing the sum of Measurement as follows:

We’ll use this similar method for nearly the entire calculated fields.

Step 6: Construct place calculated fields.

Right here we wish to outline the beginning factors and the ending level of our Max Curves and our Min Curves.

Create some calculated subject to outline Max and Min Positions:

Calculated Discipline Title: **Max Place 1**

Formulation: **RUNNING_SUM([Flow Size])**

Compute alongside Agg Step 1, Agg Step 2 (within the superior possibility of the Desk Calculation.)

Outline the Min Place.

Calculated Discipline Title: **Min Place 1**

Formulation: **RUNNING_SUM([Flow Size])-[Flow Size]**

Compute alongside Agg Step 1, Agg Step 2 (within the superior possibility of the Desk Calculation.)

Calculated Discipline Title: **Max Place 2**

Formulation: **RUNNING_SUM([Flow Size])**

Compute alongside Agg Step 2 then Agg Step 1 (within the superior possibility of the Desk Calculation.)

Outline the Min Place.

Calculated Discipline Title: **Min Place 2**

Formulation: **RUNNING_SUM([Flow Size])-[Flow Size]**

Compute alongside Agg Step 2 then Agg Step 1 (within the superior possibility of the Desk Calculation.)

NOTE – The superior choices of the Desk Calculation allow to outline the order wherein the curves will begin and wherein they are going to finish.

Step 7: Construct the curves calculated fields.

We’ll construct a Max curve and a Min Curve based mostly on Jeff’s work.

Calculated Discipline Title: **Max Curve**

Formulation: **[Max Position 1]+(([Max Position 2]-[Max Position 1])*ATTR([Sigmoid]))**

Calculated Discipline Title: **Min Curve**

Formulation: **[Min Position 1]+(([Min Position 2]-[Min Position 1])*ATTR([Sigmoid]))**

We are able to now construct our polygon curves.

Calculated Discipline Title: **Curve Polygon**

Formulation:

CASE ATTR([Min or Max]) // bear in mind the information we added to the mannequin !

WHEN ‘Min’ THEN [Curve Min]

WHEN ‘Max’ THEN [Curve Max]

END

When constructing the viz, the « path » will enable us to affix the factors to attract the polygons.

Step 8: Construct the Sankey.

Select the « polygon » graph kind and place the next drugs within the marks:

Agg Step 1

Agg Step 2

Min or Max

Path (as a path)

Place the « T » capsule in « columns » and present it as a dimension (proper click-dimension).

Lastly place the « Curve Polygon » within the « strains. »

Some ultimate touches:

Change transparency to 50%.

Clear the « T » axis to a hard and fast vary of -6 to six and conceal the axis.

Clear the « Curve Polygon » axis to a hard and fast vary of 0 to 1.

Reverse and conceal the axis.

Et voilà!

Step 9: Construct the bar charts.

Create a calculated subject:

Calculated Discipline Title: **Step Measurement**

Formulation: **SUM([Size])/TOTAL(SUM([Size]))**

Don’t specify desk calculations.

On a brand new sheet, place « Step Measurement » into rows and « Agg Step 1 » within the marks (colours or particulars). I selected to customise utilizing borders.

Conceal axis and repeat the operation for Step 2.

Now you’ll be able to construct the Sankey in a Dashboard ! After a little bit of beauty and customization, right here is the ultimate consequence (click on picture under for Tableau Public Viz):

It’s absolutely interactive and responsive!

I additionally constructed a protracted model that may be discovered on this link.

Sorry that it’s in French in addition to the Tableau file and information, however that is my native language!

**Conclusion:**

This technique has 3 benefits:

1. Few information preparation

2. No SQL or Macro required

3. Very responsive since simply exhibiting polygons

Be at liberty to enhance the tactic or email me for any questions!

Visitor Blogger,

**Olivier Catherin**

NOTE FROM JEFF:

Thanks Olivier for this wonderful work and for creating this visitor publish so as to add to our assortment on constructing sankey diagrams in Tableau.

I hope you discover this useful. You probably have any questions be happy to e-mail me at Jeff@DataPlusScience.com

Jeffrey A. Shaffer

Comply with on Twitter @HighVizAbility

*Edited by Breanne LaCamera 12/3/2014 and posted 11/19/2014*