Controlling Someone elses' Viz...

If you are like me, then you are lazy. Why design a visual when someone else has already done it?? Perhaps you just want to link to one. But perhaps, you also want to filter on one. This example makes use of simple javascript to tell a story. I've used a dashboard from The Data Studio designed by Andy Cotgreave. You will want to "view source" on this blog post to see the javascript that I used to control Andy's viz. I could have exposed the javascript somehow, but remember my comment about being lazy? Yeah... anyway, using html and javascript filters to control someone elses' viz makes me feel so... omnipotent! :)

This is my control To the right is Andy's viz Choose your American States: All A-M N-Z My Favorite States

Miniplots

Starting in 2011, I'm making a mission out of creating tiny charts and dashboards. I'm exhausted from constantly looking at big, ugly charts and dashboards that fill up the screen but are mostly filled with whitespace and other useless junk. Keep reading below the viz to see more info.

Powered by Tableau

In the two examples above: The Multigrain dashboard uses a calculated field and a parameter to change the Level of Detail (LOD) for the visual. This is *not* recommended at high data volumes as it will be quite slow. However, you should see decent performance at 5k to 5m records, depending on your data source. The nasty non-optimal calculation looks like this:


CASE [Select Granularity]
WHEN "Level 1" THEN [Prod Type1]+", "+[Segment]
WHEN "Level 2" THEN [Prod Type1]+", "+[Segment]+", "
+[Prod Type2]
WHEN "Level 3" THEN [Prod Type1]+", "+[Segment]+", "
+[Prod Type2]+", "+[Prod Type3]
+", "+[Prod Type4]+", "+[Customer Name]
WHEN "Level 4 - Warning!" THEN [Prod Type1]+", "+[Segment]
+", "+[Prod Type2]+", "+[Prod Type3]+", "+[Prod Type4]+", "
+[Customer Name]+", "+str([Order Id])+", "+str([Line Id])
END


I guess the good news here is that Moore's Law will keep catching up with this kind of lazy, non-performant analytics! :)

The second example, Quadriplot, came to me in a fitful waking state. I wanted to see how small I could make a 4-up dashboard without losing usability. It's debatable as to how successful I was. Nonetheless, the salient points to this dashboard are:

• The use of discrete Tableau Actions
• The use of the new 6.0 table calculations to show totals at the bottom

Binary Charts

I would like to come up with a better name than a Binary Chart. I also am getting a little bit tired of hearing the acronym "KPI" (Key Progress Indicator). If anyone has a better name for this type of chart, I am all ears and keyboards...

Powered by Tableau

Only Show Background Image When...

Those who know me, know me as a contrarian. Thus, with the arrival of 6.0, I'm going to highlight one of the oldest features in Tableau. This feature - known as "only show a background image when a field equals a value" does not appear to be documented anywhere. Perhaps in the Tableau user forums. I haven't had time to search... anyhoo, this happy little feature is one of my personal favorites. In the viz shown below, when you select a new value for the data set (Tableau's ubiquitous "coffee chain" data), the background image will change. I have randomly selected four satellite images, which have nothing to do with the data per se, but it demonstrates the point nicely.

Coffee
Tea
Herbal Tea
Espresso

Tableau 6.0 is released

Here are three things amongst many that I like about version 6.0. Let's start with combo charts. You can now have lines and circles and bars and shapes all on the same viz. No real limits here, except good common sense and taste. We'll be seeing lots of junk charts as a result of this feature set. You take the good with the bad. Below we have a disk drive viz, showing writes per second. The dotted line is a user-controlled moving average. Using a margin of error calculation, we have also shaded the upper and lower bands, which themselves are also controlled by the end user.)

Powered by Tableau

Next up are variables (the example above had one). These are handy little helpers to make your calcs and visuals come to life. With this new parameter concept, Tableau provides specific datatype support for dates, strings, numbers, and booleans. Tons of nifty cool things you can do with these! In the next viz we are given the ability to select the fields we want to display. This is a quick and easy way to create one single visual which can serve many different purposes.

Powered by Tableau

The third item I'm really enjoying is hierarchies in conjunction with the new super fast data engine (which replaces firebird).The viz below shows a hierarchy in action. Tableau Groups, Bins, Calculated fields and regular fields can all be added to a hierarchy. In our hierarchy, we even have a parameter-driven calculated field!

Powered by Tableau

UK Fat Cat Pay Data Made Public

One of the first items David Cameron has released is "Top Pay" for public sector employees in the UK. The data comes from http://www.cabinetoffice.gov.uk/ - the exact link for the CSV file is:
http://download.cabinetoffice.gov.uk/transparency/high-earners-pay.csv

Powered by Tableau

Plotting against a zero value

Interesting request for a visual... The selected category should exist at 0,0 and then other categories should be compared against this category. I'll write this up in further detail as time permits.

The example below shows state "Per Capita Income" and "Percentage of College Degrees" against all other states. The selected state is positioned at 0,0.

The data comes from http://www.census.gov/compendia/statab/rankings.html

More information describing how to do this is shown below the visual.

Powered by Tableau

This was basically a self join on the table, where state <> state (not equal to). Then, I was able to create very simple calcs to create the X and Y coordinates for "other states" as compared to my selected state.

Here is a picture of the join:

and here is the actual data that I used. I had to combine the two statistics (income per capita, and percent BA degrees) into one table, as the census folks don't like handing out arbitrary cohort analytics to the general public!! :)

Marking an Action as a Global Quickfilter

This is a relatively simple post to point out that you can mark Tableau actions as global quickfilters. The advantage to doing so is so that you can continue to use the action on the target sheet or dashboard. This most commonly becomes critical when you are jumping across data sources, or jumping from one Tableau dashboard to a different Tableau dashboard.

Powered by Tableau

Public Data - Seattle Restaurant Inspections

Forcing a Sort in Tableau

Note: this is largely depricated by Tableau v7.0 and later... Dynamic sorting is difficult in any application, regardless of the technology. There are business and logical reasons why this is the case. Using Tableau, there are often several ways to effectively "sort" information, and no one method is "better" or "worse" than other methods - it's purely the business situation which will dictate this. In the example below, there are several converging Tableau concepts at work, including: nested sorting, sets, hidden data, quick table calculations, calculated fields, and some light formatting. What is particularly interesting about this example is the use of hidden data to maintain accuracy with the "percent of total" calc, without losing an effective sorting mechanism.

View The Example | View The Explanation

Thematic Maps - Seattle Crime Statistics

Sometimes I get rather caught up in the various dross and droll of the geocoding and mapping subject (read: thrash) as it relates to analytics, and I forget that a good map does not have to reference actual geography to represent a theme of data... Seems like a "D'uh" moment. I must be getting old.

The data below comes from http://data.seattle.gov and this visual uses a simple police precinct image as the background.

The basic conclusion I can come to with this data is: overall, crime is quite a bit down year over year for 2008-2009. Nifty.

All Crime Data | Crime Decreases | Crime Increases

Forecast Modeling

Author's Note, Dec 2010:This blog post is now obsolete by virtue of the fact that Tableau 6.0 has "parameters" - the entire subject of data explosion or data fanning is no longer a requirement.

original blog post


Quick start cheat notes:
 

• sample data is here 
• make all joins "not equal to" 
• set all filters to single-select (and make them global just for convenience)

Read the rest of the article below this dashboard, for the full details... 
Use the sliders to interact

Powered by Tableau

 

 




A discussion about "predictive modeling", or "forecast analysis", or, "what if analysis"

This entry discusses steps required to use Tableau to create what if or hypothetical analysis models. While not currently a core feature of the product suite (with the typical resulting marketing and sales splash as such), it turns out that with a little thought, and by following a few simple rules, you can create forecast models in Tableau. For the purposes of this discussion, I have used Xcelsius as a comparison, a tool that is commonly thought of as excellent for this type of analysis.

Products that provide extensive forecasting and predictive modeling algorithms are great tools for creating "what if" or hypothetical analysis - there's a great deal of heavy lifting going on to make this magic work. Some of the technical challenges involved include (not limited to):

All of the heavy lifting or number crunching needs to occur somewhere or at some point in the overall process

The user experience and software design thereof needs to provide compelling and useful tools for managing the input and output of the modeling.

There are some good software packages out there that provide forecast modeling and predictive analysis. A few worthy mentions include SAS, SPSS, the open source "R" package, and SAP’s Xcelsius (formally owned by Business Objects, who in turn purchased it from Infommersion, Inc, see nice history link here). From anecdotal evidence it appears that Xcelsius attempts to operate on one very small amount of data at a time, and then "explodes" this data out into a much larger data set - most likely in memory. General approaches to this type of modeling scenario include pre-processing possible values in a database engine, or, performing other in-memory processing.

Tableau can achieve this "exploded" data concept by creating table joins on all possible values - essentially a Cartesian join showing all possible combinations of data. There are a few simple tricks to make this work:

1. Create all joins as "table1.fieldX <> table2.fieldY" (i.e. use the "not equal" type). Make sure no values will match, or else this does not work! See the excel file which shows the four sheets to join; as well, you can download the workbook shown above to see the joins.
2. Any time you create calculated fields, make sure that they are non-aggregation calculations. See the workbook for the calculation called "potential value". Basically, you want row-by-row calculations at all times! 
3. And most importantly! Make sure you have single-select filters for all forecast or what-if variables which are controlling the "fact" data.

What does that last item 3 really mean? Let’s take a closer look at the sample excel data:

In the example excel file, we had three categories (K-12 School District, S&P 2000, and Federal Agencies) and 6 possible values (50k through 300k) for a total of 18 possible values.

By joining on a percentage between 1 and 100, the data gets exploded out to 1800. And by adding in a second variable called "blending", which ranges from -.21 percent to .21 percent (43 possible choices including zero), this further explodes the 1800 records out to 1800 times 43 = 77,400 records.

However, because our three Tableau filters for "blending", "percentage" and "variable" are all marked as single-value selections, we will only see 3 records at a time (the original three categories).

Exploring the workbook shown above by downloading it will help convey this point.

You can now start to guess why Xcelsius and possibly other products start to perform slowly when dealing with larger data. One of the reasons is the data explode factor involved. If instead of the above numbers, I wanted to apply a two-variable what if or forecast model on 500 types of values (500 customers showing sales, 500 products showing inventory, etc), and one variable ranged from 1 to 100, the second variable ranges from -30 to +30, then my data explode result would be 500 * 100 * 61 = 3,050,000 records! No matter how you slice and dice this problem - in memory vs. pre-calculated, oracle vs. sql server or some other database, java versus dot net- you will still be faced with this concept of data "explosion"...

Tableau can handle 3 million records, by the way...

Copy and Paste Formatting

Did you know that you can copy and paste formatting between worksheets?



Select A Theme
Standard
Tahoma Black
Verdana Blue
Times Red
MS San Serif Green
Tight
Lilac
Seattle
Salmon
Niterider
Windows Classic
Kind of Blue
Lines
Brightness
Wheat
America
Oceania
Unix
Wolverine

Who is Alan Smithee?

Alan Smithee is the fake name used by film and movie directors when they want to completely disassociate themselves from the final production of their movie or film... much like I desire to disassociate myself from this blog!! :)

---

A list of potentially terrible movies directed by "Alan Smithee" is located here.

Alan Smithee's Bio is located here.

ALL content on this blog is specifically authored and controlled by the one and only Alan Smithee.

Differences between sheets and dashboards when printing and using PDF output

This is just a quick example to show the differences between a Tableau sheet and a Tableau Dashboard, specific to the goal of printing or using PDF output. By definition, Tableau dashboards are best considered "single page entities". In contrast, a Tableau sheet (or view) can span multiple pages. This makes logical sense.

In Tableau desktop, for an individual sheet, you can specify portrait or landscape mode for that sheet, from the File>Page Setup command. This setting will be honored once you publish to Tableau Server.

For example, the following link will auto-generate a PDF of a multi-page report. Notice that the output is already set to "landscape" mode:

Click here to view the multi-page PDF report

Whereas, this second link is the same exact information, but placed onto a dashboard which has been sized to 1100 x 850 (letter landscape). It will only print one page, and thus, a good chunk of the information is cropped and not visible:

Click here to view the single-page PDF dashboard

Again, this is all quite logical: it doesn't really make any sense to print a multi-page dashboard. Else, why call it a dashboard in the first place?

Tableau Linking to Google Calendar

After way too much time wasted and general hacking around... I kind of / sort of figured out how to link from a Tableau dashboard to a Google calendar. I still cannot figure out how to jump directly to an "add new event" screen, which would be super cool! Their calendar API seems rather limited still, at least from a pure URL perspective.

At the heart of this example is the fact that I created a public google calendar and then tried to understand the possible variables I could insert into the URL. This part turned out to be rather undocumented, and it's quite possible that google will change this over time, thus breaking this example. If you download the workbook, you will see a Tableau "action" URL which includes the fields from the example data inside the URL.

I also had to create a calculated field called "stringy month" which pads the month integer with a zero for months 1 through 9, simply because google is apparently not smart enough to deal with this. Again, you can download the entire workbook shown below to see this all in action. It's easier to see than it is to type.

To begin: click on one of the heat map squares shown below. Each square is a month and a year. Hover over each one to see the date, total fictitious sales, and fictitious profit.

Powered by Tableau

Table of Contents in Tableau

You can create a table of contents and then use that as a data source and actionable link on a Tableau Dashboard. In the example below, I started with an excel file which contained the Name, Author, and URL for various visuals already on the web. I then connected to this excel file in Tableau, and created a dashboard where the action target is the URL of the visual.


Just for fun, I set all of the links to use the PNG picture output of the original visual. You can always create a dynamically generated PNG image from any Tableau visual simply be adding ".png" to the end of the URL. The same concept applies for PDF output.

Select a row below to view the PNG output of the original visual. Go to Tableau Public to view any of the original visualizations, all of which are interactive.

Powered by Tableau

Radial Charts

shown below are the calculations for generating a cartesian X and Y coordinate system based upon a "direction" measured in degrees from 1 to 360 and a "speed" measured in wind speed. The result is a powerful mechanism to display wind charts which can be combined with time series or other analysis types, and in general can leverage the rest of the Tableau features.

You only need to know the direction (in degrees) and the wind speed.

The excel file which this book is connected to contains 100% ficticious data. No Wind Farm Corporations were harmed in the making of this workbook.

Powered by Tableau

The X calculation for showing "east west" is:

COS(RADIANS([Direction]))*[Speed]

And the Y calculation for "north south" is:

SIN(RADIANS([Direction]))*[Speed]

Multiple Levels of Aggregation

In the visual below, we needed to A) get all of the averages across all hours, B) find the minimum average of these, and C) compare the rest of the averages to the minimum average. Custom SQL as part of the initial Tableau connection is the best solution here.

In the example below, the client wanted to see - for each utility registrant and each hour of the day - the averages of energy use, but also wanted to see the various averages as compared to the lowest average (I.E. the min of the averages) This solution also allows us to see percentage bands above this minimum. Plus, if you click on a bar segment you can see the exact percentage above the minimum. Nifty!

Powered by Tableau

EIMS - Water Samples for Snohomish Watershed using Tableau

This data came from EIMS, their website is here:

http://www.ecy.wa.gov/eim/

A few notes:

• I culled data for only the last ten years, and only for the snohomish area.
  
• The contractors who actually performed the contaminant samples often used different units of measure, as a result of using different testing methods. So I have placed a filter for UOM to help verify the test results. In plain english: the time trend and its 'median' value reported is bogus information if you mix your UOM types.
  
• I removed any concept of what testing method they used, what the baseline observable measurement was, etc.
  
• you can select a sample type, then lasso some data marks on the timeline to see individual sample reports.
  
• The Tableau tooltips are nicely formatted; take a look at those!
  
• clicking on the "links" item will show a bing map of the sample location. Hovering back over that same selected link item will also show two other links, to NIH.gov and wikipedia (for the contaminant in question)
  
• In short: this was mostly an experiment with Tableau features, as opposed to a deep dive into water quality and contaminant analysis.
  

Powered by Tableau

Tableau Dashboard - small widget dashboard

With only a little bit of thought and care, you can create extremely polished dashboard widgets like the one shown below. I used just a small amount of HTML code to create a simple navigation scheme shown on the left below, which in turn points to various published visuals. Nice and easy!

Marketing Finance Operations Map

Tableau Dashboard - Multi-Language Support

The following is a dashboard using microsoft's adventureworks data, which happens to include support for multi-byte languages such as arabic. Tableau supports multi-byte and extended character sets inside of all visualizations. Now, if only I could actually read arabic, this view might mean something to me...

If you speak arabic and see that there is something wrong from a technical point of view, let me know. If the translation is just 'terrible', then let microsoft know.

Powered by Tableau