Exposed by Tableau

Editor’s note: today we have a guest article written by John Weinshel, who has been quietly contributing to the FileMaker community for 20+ years. John’s thoughtful postings have helped developers at all levels of expertise, and I’m pleased to present his thoughts on Tableau here on FileMaker Hacks.

Demo files: exposed-by-tableau.zip (6Mb, contains two .twbx files)
Tableau trial: https://www.tableau.com/products/trial

The Tableau connector widget for the FMS 16 data API has made developers aware of the incredible interactive charts and graphs in Tableau. What may be less obvious is that Tableau can also tease out data that is hard to see natively—independently of the cool graphs.

FileMaker data is about a thing, an entity—the customer, stock, auto part, invoice item, reservation. With some additional work, we can see patterns in groups of data, but the default is to deal with it at the row level.

Visualization tools like Tableau, on the other hand, start out with groups of data. With some additional work, Tableau can elicit information about individual records, but the default is to work at the aggregate level. One of its strengths is, in fact, revealing patterns about groups of groups.

This post looks at how Tableau can—apart from its dazzling graphics—reveal hidden information within FileMaker data. It does not get into how to build calcs and views, nor how to connect Tableau to FileMaker data; some suggestions for learning about Tableau appear at the end of this article.

Let’s use as a first example a data set of sales that appeared on these pages in July 2017 (Virtual List Reporting, part 3). The included test file generates about 70K sales records, randomly assigned to a date, customer (and, thereby, state), and type (walk-in, phone, internet); Kevin’s article describes an elegant method to build complex reports on the fly, without adding any helper fields or table occurrences, using Virtual Lists.

The reports answer questions we commonly ask of such data:

• Show all sales, within one month, sorted by state.
• Show all sales, within one month, sorted by the number of sales per state.
• By profit or
• By markup
• Smaller found sets of all above, limited to top 10.
• Same idea, but by customer rather than state, broken out by type of sale.

Here are the top 10 states by sales for December 2014, in FileMaker:

Here’s a Tableau version of the same report (the actual data varies from the pictured FileMaker charts because of the random generators):

So far, no big differences.

What if we wanted to see the top 15 states, instead of 10? Not too hard. What if we wanted a slider or pull-down for top n? A little harder in FileMaker, simple in Tableau.

What if we wanted a picker for both the top n number of states (or customers, or something else), and a picker for which field (total sales, profit, markup, number of sales) is used as the criterion? Still possible in FileMaker, but it’s getting a bit more complex. Here’s a Tableau version:

Notice how ties are managed, and think about all the time you’ve spent working out methods to apply different business rules to ranking. Tableau provides native functions to rank ‘competitively’ (shown), or without numerical gaps, or with forced uniqueness, or by percentage.

And we’re still looking at tabular data—no fancy graphics.

Now let’s ask a different question of the data, one that would be a challenge for FileMaker to answer on the fly: If we define ‘cohort’ as customers whose first purchase is in a given quarter, how many of a given cohort re-purchase within n quarters? Here’s a text (tabular) version of Tableau’s answer:

The numbers within the grid are the number of customers of the cohort (y axis) making n additional purchases, X quarters out, where X is the X axis. So 856 customers whose first purchase was Q4 ’09 made a further 3 purchases.

Rather than try to read those little numbers, we could have Tableau show the big picture using what it is more known for– cool graphics (the numbers still display on hover).

Before we leave this data set, let’s ask it one more question:

Define the cohort as customers whose first purchase was in a given year. How many of the cohort for 2012 made n purchases in 2013? In the screenshot below, each line is a cohort. The Y axis is the number of customers who have purchased n times in the selected year, where n is the X axis. 77 customers from the 2010 cohort, for example, made 3 purchases in 2013.

Here’s the same report presented in a more Tableau-like manner:

In the view above, you can select the cohort, which are color-coded lines, from the checkbox upper left.

Our last example looks at a different data set: US newborns’ first names, looking at birth records by state since 1910. Download the free file from https://catalog.data.gov/dataset/baby-names-from-social-security-card-applications-data-by-state-and-district-of-/resource/a36f0ef8-7431-4e3c-8038-d04e2a4e3c1d

…and import a few states into a FileMaker file. I’ve picked CA, TX, and AK.

Then ask FileMaker to display the most popular names, broken out by state and gender.

Then break it out by decade.

Then, instead of the first most popular name, ask FileMaker what were the third and 50th most popular names. How about adding a checkbox picker for which ranks you want to see?

In the Tableau screenshot above, the 1st and 10th most given names are selected.

We are scratching the surface here; even within these narrow rows, we could create regions from the states, and build maps that tell us about the arc of names’ popularity. By 2010, those Marys have become Emily and Sophia; how can we visualize that journey?

Tableau offers us a powerful graphical environment (as do charting plugins and JavaScript). But it also offers us tools to reveal data, particularly patterns in groups of data, that are otherwise difficult if not impossible to view in FileMaker.

Further Learning

A couple of points to note about the Tableau world:

1. It’s a much younger product (b. 2003) than FileMaker and even its non-integer releases are not always entirely backwards-compatible. Don’t be alarmed if tutorial examples don’t quite match your screen. The contents of their Superstore and other test files have varied over the years.

2. Little reverence is paid to object (field, file, worksheet, dashboard) naming conventions. # of Students / # of Classes (- summer and 4/16/15) would not be a surprising name for a calc.

3. It handles windows/file focus differently than FileMaker. Quit/Exit leaves other Tableau windows open, and multiple windows are unaware of each other.

Books

Packt,(www.packtpub.com) a Mumbai based publishing house, seems to have cornered this market, publishing guides by both Indian and Western authors. One of the most comprehensive, by the former, is Tableau Cookbooks-Recipes for Data Visualization, by Shweta Sankhe-Savale. The book’s rigid format and formulaic, repetitive language can obscure the thorough and thoughtful insights she brings to each feature she discusses.

I’ve also learned a lot from David Baldwin’s Mastering Tableau, as well as Learning Tableau by Joshua N. Milligan.

Packt pushes cloud-based sales, and I no longer see a way to purchase either ebook or hard copies, on demand, from their site, although Amazon still sells hard copies. Don’t know if they’ve stopped, or you can still contact them for non-cloud versions.

None of these is, say, Eloquent Javascript (a beautifully written book), or a Missing Manual, but they will yield to the thoughtful reader.

Online

Reference

https://onlinehelp.tableau.com/current/pro/desktop/en-us/reference_overview.html

Community

https://www.tableau.com/community

The online community is generally friendly and quick to help. The standard exchange is for the original poster to put up their problem file on the online site, and for responders to post their solution files. As with any developer forums, use some discretion in viewing the responses as gospel. It’s a large, active group, so a live subscription returns a fire hose of posts. Membership is free.

FileMaker and Tableau

Vince Mennano has been an early and generous champion of coupling FileMaker to Tableau:

https://blog.beezwax.net/2017/04/20/filemaker-tableau-building-dashboards-with-interactivity/

https://blog.beezwax.net/2017/05/09/filemaker-16-tableau-a-match-made-in-data-heaven/

John Weinshel has been using Filemaker since the flat file days of the early nineties, and has managed his one man shop, Datagrace, from Vashon Island, WA since 2000. Before that, he designed and built custom hardwood furniture for private clients. He has been certified for most versions of FileMaker, including 16.