Zebra Charts

Zebra charts are way to compare two dates for a single data point and graph the differences between these dates. For various reasons a person, or event, or transaction could begin at X time and have another event at Y (which doesn’t necessarily come after, it could be before). I have been using these charts for the last month at work and they have been very informative to diagram events. Of course these are just a type of Bar or Area chart, but the ‘Zebra’ work comes from the striping.
The inspiration for these charts comes from Cox Communications and their demonstration for Tableau. They looked at the difference between deal closing date and the installation date:  https://youtu.be/J7zMXBl0hJw?t=9m28s
In this image, the day the sale was booked is a color, and the bottom axis is install date:

ZebraChart_source

We can see that sales for the month of August (right hand side) are influenced by many previous weeks decisions.

We can take a metric for this week, and visualize how events from past weeks have influenced this behavior. For example, suppose that the sales this week are $100. Then this zebra chart van show that $60 was collected from orders placed last week, $30 for two weeks prior, $10 3 weeks prior

Possible Business reasons:

  • This could be the difference between when the sale was booked and when the money was actually collected.
  • When a customer ordered vs when the order was canceled.
  • When a service actually occurred vs when it was scheduled to occur.
  • When a customer arrived vs when they said they were going to arrive (which could be before or after).

Full Video of Cox’s presentation: https://www.youtube.com/watch?v=J7zMXBl0hJw

Linked here is a full workbook showing the example I made.

Here’s a finished example we used here at work. Valentine’s day is highlighted. You can see that 77% of event #2 fell on the same day as event 1. There is a long tail going after, and a very small number of event #2 actually came before the day.

Zerbra_Chart_Final_Example

I suppose one could also call these type of charts “Tiger Charts” if you’re looking for a ferocious name.

Some Additional Color Palettes for Tableau

Last year, while attending one of the Tableau User groups,  I learned how to create custom color palettes.  I have accumulated quite a few over the last year and I thought I’d like to pass them on.   Several of them are useful for heatmaps.  This first set is called Spectral Color and this is based on the colors of the stars, which in turn is based on their temperature.  I acquired this from an ATUG meeting and I have found it useful for heatmaps.

Spectrals

<color-palette name="Spectral 3 Color Div Palette" type="ordered-diverging"> <color>#BC80BD</color> <color>#CCEBC5</color> <color>#FC8D59</color> <color>#FFED6F</color> <color>#FFFFBF</color> <color>#99D594</color> </color-palette>
<color-palette name="Spectral 4 Color Div Palette" type="ordered-diverging"> <color>#D7191C</color> <color>#FDAE61</color> <color>#ABDDA4</color> <color>#2B83BA</color> </color-palette>
<color-palette name="Spectral 5 Color Div Palette" type="ordered-diverging"> <color>#D7191C</color> <color>#FDAE61</color> <color>#FFFFBF</color> <color>#ABDDA4</color> <color>#2B83BA</color> </color-palette>
<color-palette name="Spectral 6 Color Div Palette" type="ordered-diverging"> <color>#D53E4F</color> <color>#FC8D59</color> <color>#FEE08B</color> <color>#E6F598</color> <color>#99D594</color> <color>#3288BD</color> </color-palette>
<color-palette name="Spectral 7 Color Div Palette" type="ordered-diverging"> <color>#D53E4F</color> <color>#FC8D59</color> <color>#FEE08B</color> <color>#FFFFBF</color> <color>#E6F598</color> <color>#99D594</color> <color>#3288BD</color></color-palette>
<color-palette name="Spectral 8 Color Div Palette" type="ordered-diverging"> <color>#D53E4F</color> <color>#F46D43</color> <color>#FDAE61</color> <color>#FEE08B</color> <color>#E6F598</color> <color>#ABDDA4</color> <color>#66C2A5</color> <color>#3288BD</color> </color-palette>
<color-palette name="Spectral 9 Color Div Palette" type="ordered-diverging"> <color>#D53E4F</color> <color>#F46D43</color> <color>#FDAE61</color> <color>#FEE08B</color> <color>#FFFFBF</color> <color>#E6F598</color> <color>#ABDDA4</color> <color>#66C2A5</color> <color>#3288BD</color> </color-palette>
<color-palette name="Spectral 10 Color Div Palette" type="ordered-diverging"> <color>#9E0142</color> <color>#D53E4F</color> <color>#F46D43</color> <color>#FDAE61</color> <color>#FEE08B</color> <color>#E6F598</color> <color>#ABDDA4</color> <color>#66C2A5</color> <color>#3288BD</color></color-palette>
<color-palette name="Spectral 11 Color Div Palette" type="ordered-diverging"> <color>#5E4FA2</color> <color>#9E0142</color> <color>#D53E4F</color> <color>#F46D43</color> <color>#FDAE61</color> <color>#FEE08B</color> <color>#FFFFBF</color> <color>#E6F598</color> <color>#ABDDA4</color> <color>#66C2A5</color></color-palette>

Kris Palette.  I have built these myself with inspiration from the iPhone app called Fractal Plus.  These palettes have an excessive number of colors (you really only need about 7 and Tableau can do the rest), but this allows for others the customize the amount of drift between the major colors.  So if these are black-heavy, just remove some of them to push the middle left, or remove some of the reds to push them right.
Burnt
Burnt

<color-palette name="Kpal Burnt Blk-Org-Red div" type="ordered-diverging"> <color>#242422</color> <color>#272624</color> <color>#322e25</color> <color>#3b3529</color> <color>#453c2b</color> <color>#4e412e</color> <color>#584831</color> <color>#604f35</color> <color>#695638</color> <color>#725b3a</color> <color>#84673f</color> <color>#9e7a49</color> <color>#a47f4b</color> <color>#b28952</color> <color>#c29556</color> <color>#d19e5c</color> <color>#e3ab60</color> <color>#e4ac5f</color> <color>#e6a95c</color> <color>#e6ab5e</color> <color>#e7a351</color> <color>#ec9b40</color> <color>#e74e24</color> <color>#e34b2a</color> <color>#d03a2a</color> <color>#de3224</color></color-palette>

Burnt_Ash

<color-palette name="Kpal Burnt Blk-Org-Red-Gry div" type="ordered-diverging"> <color>#242422</color> <color>#453c2b</color> <color>#584831</color> <color>#695638</color> <color>#84673f</color> <color>#a47f4b</color> <color>#c29556</color> <color>#e3ab60</color> <color>#e6a95c</color> <color>#e7a351</color> <color>#e74e24</color> <color>#d03a2a</color> <color>#de3224</color> <color>#B45850</color> <color>#A3918F</color> <color>#B6A7A5</color></color-palette>

City Night
CityNight_Cycl

<color-palette name="Kpal City-Night cycl" type="ordered-diverging"> <color>#101825</color> <color>#222d43</color> <color>#333d60</color> <color>#465376</color> <color>#55658a</color> <color>#6d7693</color> <color>#908d86</color> <color>#b4a48c</color> <color>#d9bb88</color> <color>#f8cd76</color> <color>#f4b872</color> <color>#ee9a6b</color> <color>#a95e59</color> <color>#7c4f56</color> <color>#463d58</color> <color>#313250</color> <color>#202948</color> <color>#101d30</color> <color>#0b131e</color></color-palette>

CityNight_R

<color-palette name="Kpal City-Night div" type="ordered-diverging"> <color>#101825</color> <color>#222d43</color> <color>#333d60</color> <color>#465376</color> <color>#55658a</color> <color>#6d7693</color> <color>#908d86</color> <color>#b4a48c</color> <color>#d9bb88</color> <color>#f8cd76</color> <color>#f4b872</color> <color>#ee9a6b</color> <color>#a95e59</color>

CityNight_O

</color-palette> <color-palette name="Kpal City-Night Orng div" type="ordered-diverging"> <color>#101825</color> <color>#222d43</color> <color>#333d60</color> <color>#465376</color> <color>#55658a</color> <color>#6d7693</color> <color>#908d86</color> <color>#b4a48c</color> <color>#d9bb88</color> <color>#f8cd76</color> <color>#f4b872</color> <color>#ee9a6b</color> <color>#FF985C</color></color-palette>

Fire:
Fire_Cyclic

<color-palette name="Kpal Fire cycl" type="ordered-diverging"> <color>#f9f9f9</color> <color>#e0e7fa</color> <color>#b4bad0</color> <color>#758295</color> <color>#323e49</color> <color>#2f3841</color> <color>#15160b</color> <color>#1a1100</color> <color>#3f0e00</color> <color>#7a0901</color> <color>#9a0601</color> <color>#ba0400</color> <color>#da0100</color> <color>#f80600</color> <color>#fa1c06</color> <color>#fc5212</color> <color>#f8e24e</color> <color>#f7fc66</color> <color>#f7fd81</color> <color>#ebe38e</color> <color>#debe79</color> <color>#c79058</color> <color>#b04f2f</color> <color>#830303</color> <color>#580902</color> <color>#320e02</color> <color>#1e1005</color>

Fire_Red

</color-palette> <color-palette name="Kpal Fire Red div" type="ordered-diverging"> <color>#f80600</color> <color>#fa1c06</color> <color>#fc5212</color> <color>#f8e24e</color> <color>#f7fc66</color> <color>#f7fd81</color> <color>#ebe38e</color> <color>#debe79</color> <color>#b04f2f</color> <color>#580902</color> <color>#1e1005</color></color-palette>

Fire_RedBlue

<color-palette name="Kpal Fire Blue div" type="ordered-diverging"> <color>#41dfff</color> <color>#68b0c8</color> <color>#8291a3</color> <color>#8f8291</color> <color>#c34448</color> <color>#f80600</color> <color>#fa1c06</color> <color>#fc5212</color> <color>#f8e24e</color> <color>#f7fc66</color> <color>#f7fd81</color> <color>#ebe38e</color> <color>#debe79</color> <color>#b04f2f</color> <color>#580902</color> <color>#1e1005</color>

Fire_RedBlueWhite

</color-palette> <color-palette name="Kpal Fire Blu-Wht div" type="ordered-diverging"> <color>#f1fcff</color> <color>#c8f5ff</color> <color>#92ecff</color> <color>#69e5ff</color> <color>#41dfff</color> <color>#68b0c8</color> <color>#8291a3</color> <color>#8f8291</color> <color>#c34448</color> <color>#f80600</color> <color>#fa1c06</color> <color>#fc5212</color> <color>#f8e24e</color> <color>#f7fc66</color> <color>#f7fd81</color> <color>#ebe38e</color> <color>#debe79</color> <color>#b04f2f</color> <color>#580902</color> <color>#1e1005</color></color-palette>

Starry Night
StarryNight_Cycl

<color-palette name="Kpal Starry Night cycl" type="ordered-diverging"> <color>#fcfdf0</color> <color>#fbf4a3</color> <color>#fcbe27</color> <color>#8a401a</color> <color>#4a0927</color> <color>#27063f</color> <color>#0e0153</color> <color>#01025f</color> <color>#082487</color> <color>#0f3598</color> <color>#2a65c1</color> <color>#5282cc</color> <color>#99b4e4</color> <color>#e0e7f9</color></color-palette>

StarryNight_W

<color-palette name="Kpal Starry Night div" type="ordered-diverging"> <color>#fcfdf0</color> <color>#fbf4a3</color> <color>#fcbe27</color> <color>#8a401a</color> <color>#4a0927</color> <color>#27063f</color> <color>#0e0153</color> <color>#01025f</color> </color-palette>

Using Tableau’s Command Line Extract tool, some hangups

Recently I have been exploring how to use Tableau’s command line tool to update published extracts on Tableau Server.  “Productinizing” more and more aspects of Tableau.  This is an easy way to trigger an extract to update after the job has completed.  The problem with schedules in Tableau is that by definition they are schedules, and sometimes your data just doesn’t come in on a predictable schedule.  You could trigger a refresh manually by using “Add Full Refresh” option under scheduled tasks and pressing “(Run Now).”  However if you learn how to use the tableau command line, then you could put it into a batch file and run it automatically from a server.  SQL server can execute a batch file as a job step, and it can also execute it from a stored procedure.

Here is Tableau’s online documentation for this feature:
http://onlinehelp.tableau.com/current/pro/online/mac/en-us/extracting_TDE.html

Additional features are listed here.  Apparently there is also way to print to a default printer!
http://community.tableau.com/servlet/JiveServlet/previewBody/5209-102-2-5623/Tableau%20Command-Line%20Parameters.pdf

Now there are some pitfalls that I have come across and wanted to share them so it could potentially decrease headaches.  Specifically I encountered this nice error:

TableauDataEngineError4

Tableau Data Engine Error: 4: Invalid Command: database: Path does not exist

Possible Problem #1) The data source must me made with the current version of the server.

So lets say you build a cool report and you schedule it.  It runs perfectly for dozens of weeks.  You download 8.2 or 9.0 and forget about this report.  Ages later you come around to needed to update that extract via the command line program.  You will encounter that nice message above.  Um, yeah so which path is that?  The server? The Site? The Project? or the datasource? After searching the internet, I found a few things that could have been the problem.  Finally I read something that said my extract was made with a past version of Tableau.  I knew that had to be the case because I am very eager to update my Tableau desktop versions as soon as new ones are released.

Solution:  Create the extract from scratch. 

Now if you are like me and use fewer SQL formulas and more Tableau formulas then the thought of duplicating all the groups / sets / calculations / parameters into a new dataset is boring and lame.  Fortunately the amazing people at Tableau made copying and pasting calculated things extremely easy.  All you need to do is open up the new extract and the old.  Right click the calculated field and choose “copy”, (DO NOT CTRL+C YOU SLACKERS).  Then go into the other dataset, right-click and press “paste”.  Boom!  Simple.  Hold down control, select several fields, menu-copy, menu-paste and Boom “That was easy”, to quote Staples.

Possible Problem #2)  Datasource is live.  It must be an extract.

This is obvious and should be an easy fix.  I almost exclusively use and publish extracts.  This is because most data I work with is fairly large.  If however if you are publishing many data sources, you may occasionally forget which ones are extracts and which ones are live connections.  This tripped me up once.  I was certain that it couldn’t be problem #1 because I had just made and published the dataset, but I got the same error.  This makes sense, since the extension for a Tableau Data Extract (.tde) is not the same again as a data source connection ().

Solution: Make it an extract :)

This is what success looks like:

TableauDataEngineSuccess

Native Hexbin support in Tableau 9

Yes! Yes! Yes!  I could type that many more times, but I’ll get to the point.  Tableau 9.0 has two new formulas that enable hexbin support!

HEXBINX(x,y)
HEXBINY(x,y)

Now the thing is that these formulas have a unit length of “1” so you will have to scale them yourselves.  So for instance this is what a basic map of the SE USA looks like w/o scaling.

TableauBeta9Hexbin

Although I can’t put latitudes on a Tableau Map, it generally appears that the length of 1 roughly corresponds to one latitude and longitude.  If you need Here is the formula to scale the value up x10 ,  do the hexbin magic, and scale back down.  First I created two different ZoomLvl parameters for you to use to adjust the number of hexbins.  One for X and one for Y.  This will give you the ability to change the # of hexbins in either direction.  This could be useful.

HEXBINY([LONGITUD]*[ZoomLvl_Y],[LATITUDE]*[ZoomLvl_Y])/[ZoomLvl_Y]
HEXBINX([LONGITUD]*[ZoomLvl_X],[LATITUDE]*[ZoomLvl_X])/[ZoomLvl_X]

Now you can see that there is a much higher hexagon resolution.  It’s a heatmap of Accidents.  I will be posting a completed workbook later.

SE_USA_heatmap

Of special note is this: if you are going to try to save steps and copy the field (say latitude) and replace the formula with the other one then rename it “longitude” then Tableau will keep the measure’s geographic role assigned to “latitude”.  Change this to longitude!

Geographic Role

Now here is an example of how HEXBINS can help with an ordinary scatterplot.  This data below was generated from these two functions.  This formula would work in Tableau, however since the random() function in Tableau has a fixed seed, it will not generate different random data for two columns. Stringing multiple randoms together makes a pseudo normal distribution.

x = iif(random()>.5,(random()+random()+random()+random()+random()+random()+random()+random()-3)/16  ,(random()+random()+random()+random()+random()+random()+random()+random()+3)/16 )
y = iif(random()>.5,(random()+random()+random()+random()+random()+random()+random()+random()-3)/16  ,(random()+random()+random()+random()+random()+random()+random()+random()+3)/16 )

You should get a cool pic like this:

4Cluster

Now even with small sizes, and 75% transparency, you will notice some of the points are obscured by the amount of data.  Hexbinning can solve this by bucketizing the data.  Here are the hexbin formulas I used:

HexbinX([F1]*[ZoomLvl_X],[F2]*[ZoomLvl_X])/[ZoomLvl_X]
HexbinY([F1]*[ZoomLvl_Y],[F2]*[ZoomLvl_Y])/[ZoomLvl_Y]

and placing the above formulas will give you a nice simple mapping like this:

4Cluster_Hexbin

Now you can clearly see the center.

Three things will help you in doing Hexbins in Tableau.  They are all custom shapes.  The first one is a hollow hexagon with a thicker border.  The second has a thin border.  And the third shows a filled hex.

Hexagon_M_Thicker Hexagon_M_Hollow Rot90 Hexagon_M_Filled Rot90

Update 3/5/2015:

I have decided to add in 6 hexagons so you can choose the level of fill that you need.

Hex6 Hex5 Hex4 Hex3 Hex2 Hex1