#Workout Wednesday – Fiscal Years + Running Sums

As a big advocate of #WorkoutWednesday I am excited to see that it is continuing on in 2018.  I champion the initiative because it offers people a constructive way to problem solve, learn, and grow using Tableau.

I was listening to this lecture yesterday and there was a great snippet “context is required to spark curiosity.”  We see this over and over again in our domain – everyone wants to solve problems, but unless there is a presented problem it can be hard to channel energy and constructively approach something.

Last pre-context paragraph before getting into the weeds of the build.  I enjoy the exercise of explaining how something works.  It helps cement in my mind concepts and techniques used during the build.  Being able to explain the “why” and “how” are crucial.

Let’s get started.

High level problem statement or requirement: build out a dashboard that shows the running total of sales and allows for the user to dynamically change the start of a fiscal year.  The date axis should begin at the first month of the fiscal year. Here’s the embedded tweet for the challenge:

 

Okay – so the challenge is set. Now on to the build. I like to tackle as many of the small problems that I know the answer to immediately. Once I get a framework of things to build from I can then work through making adjustments to get to the end goal.

  • numeric parameter 1 to 12 for the fiscal year start
  • calculation to push a date to the right fiscal year
  • dimension for the fiscal year
  • transformation of date to the correct year
  • running sum of sales

I’ll spare you the parameter build and go directly into the calculations that deal with the fiscal year components.

Logic as follows – if the month of the order date is less than the fiscal year start, then subtract one from the year, otherwise it’s the year.  I can immediately use this as a dimension on color to break apart the data.

The next step would be to use that newly defined year with other elements of the original date to complete the “fiscal year” transformation.  Based on the tooltips – the year for each order date should be the fiscal year.

Now that the foundation is in place, the harder part is building a continuous axis, and particularly a continuous axis of time.  Dates span from 2013 to 2017 (depending on how you’ve got your FY set up), so if we plotted all the native dates we’d expect it to go from 2013 to 2017.  But that’s not really want we want.  We want a timeline that spans a single year (or more appropriately 365 days).

So my first step was to build out a dummy date that had the SAME year for all the dates.  The dates are already broken out with the FY, so as long as the year isn’t shown on the continuous axis, it will display correctly.  Here’s my first pass at the date calculation:

That gets me the ability to produce this chart – which is SO CLOSE!

The part that isn’t working is my continuous axis of time is always starting at 1/1.  And that makes sense because all the dates are for the same year and there’s always 1/1 data in there.  The takeaway: ordering the dates is what I need to figure out.

The workaround?  Well the time needs to span more than one year and specifically the start of the axis should be “earlier” in time.  To achieve this – instead of hard coding a single year 2000 in my case, I changed the dummy year to be dependent on the parameter.  Here’s the result:

Basically offset everything that’s less than the chosen FY start to the next year (put it at the end).  (Remember that we’re already slicing apart the data to the correct FY using the previous calculations.)  The combination of these two calculations then changes the chart to this guy which is now mere formatting steps away from completion.

The full workbook is available to download here.

Tableau + Python + IoT

You’ve asked and it’s time to answer.  About a week ago I posted a teaser video on Twitter that showed how I integrated my home automation system with a Tableau dashboard.  The outcome of the video was dynamically changing three different lights in conjunction with KPI results.

So beyond the “hey this is really cool” factor of connecting lighting fixtures (or anything else for that matter) to a Tableau dashboard – there are some great practical reasons to do this.  A few great replies to the tweet were of the same sentiment of the thoughts coursing through my mind.  Use something like a light to easily indicate a status – it’s that step back from the KPI shape or the BAN (big ass number).  It’s something visible that you can glance at to immediately understand performance.

Next comes the technical components of how this gets put into place.  To achieve integration between my home automation system and Tableau I used Python.  It serves as a great integrator and took an idea to proof of concept quickly.  Python benefits this situation because several people have developed libraries and published them on GitHub to share.  And the other reason why Python was a great natural choice is because of TabPy.  TabPy allows you to connect to a Python server and run native Python code through calculated fields.  These calculated fields are served up on a Tableau sheet as a table calculations.  And in general when you’re using Python with Tableau it’s to get a number/string/boolean result out.

So just because you are supposed to use TabPy for something doesn’t mean you can’t extend it’s capabilities and use it in a completely different manner.  And that’s exactly what this integration project was about.

This project is also about taking a continuous project I have around home automation and folding in something new I learned (Python) with something I already love (Tableau).

My final caveat is this: if you’re looking to emulate what I did you’re going to need some hardware aka Philips Hue lights and a bridge.  In theory you can change this out with the home automation pieces of your choice.  If you’re unfamiliar with what home automation is – it basically works by having a controller (often called a bridge) and then different connected elements.  The controller is responsible for issuing commands to things like lightbulbs, locks, and your thermostat.  It is also the vehicle that takes how you interface with your home automation system (which is typically a mobile app) and translates those commands, routines, or programs to the elements in the system.

And specific to this project, I’m interfacing with my bridge (controller) using a Python library.  The library I chose to use was Phue, a Python library designed to fully interact with the Hue API.

First I downloaded and installed Phue into my Tableau-Python-Server environment (aka the one used for TabPy).  That allows me to call the library in my Python scripts that are passed through Tableau.  During the install process I also ran a simple script to connect the library to my bridge – a necessary dependency to get this all to work.  It makes sense that you’d have to be able to establish a link between the communication component and the lights you want to change.

The next parts are all reading through documentation, trial and error with Python, and fun with Tableau.  Here’s the final calculated fields that are driving results:

Profit Ratio KPI – this is something we’re all used to.  I’ve taken the basic Profit Ratio calculation and had it evaluate against a parameter.  Based on the evaluation the result will be either 0 or 25000.  The numbers 0 and 25000 correspond to the color hue input required for the lights.  0 is red, 25000 is green.

And here’s the calculated field that is pure Python.  In this scenario I’m not actually using Python to crunch anything and return a result, so you’ll notice that the return argument is a dummy number 1.

You will see it in the calculated field description in the editor within Tableau – to use SCRIPT_INT (or any of the SCRIPT functions) the format is: put your code in quotes and any inputs or arguments being used are called using _arg1, _arg2, _argN.  Those arguments follow the comma after the script.  In my example I have one argument, [Profit Ratio KPI] and that is being assigned to a in the code.

  • The basic outline of the code is as follows:
  • set [Profit Ratio KPI] to be a
  • import phue library so we can use it
  • use my bridge (that’s the bridge’s IP address on my internal network)
  • set the saturation of the lights to 254 – this is extra code, but ensures that the light has full saturation
  • set the light hue to the value of a
  • return 1 – just for fun!

If you’re extra interested, the functions from Phue require you to select which light you’re going to be controlling.  The Profit Ratio KPI light was number 25.

A trial and error moment here.  As I was testing this code I had a Jupyter notebook up and was entering the same code snippets to test it outside of Tableau.  Under normal circumstances I was able to assign a number to a without any issue, execute the code, and have the lights change colors.  Inside of the Tableau space I had to call the first index “[0]” of the argument.  I haven’t looked through the documentation for TabPy in enough detail, but it leads me to believe that arguments come in as lists instead of single variables.  So to isolate out the one result I wanted, I had to specify which element I wanted in my list of one variable.  So my KPI result is really like [25000] in Python notation and has to be called as _arg1[0] (non-Python folk: Python indexes start at 0).

Literally everything else about this was setup and build of the Tableau components.  Building out the BANs and adding parameters to dynamically input the goal variables.  Everything else was connected and working after the correct code was entered.

Here’s what one of the BAN sheets looks like:

The calculated field is present on detail, but not used anywhere else in the view.  (Which if you hovered over a default tooltip would show “1”).

Once the visualizations are set it’s just a matter of interacting with the dashboard to start seeing this integration take effect.  You’ll notice during the video that it’s not super fast – there is definitely an element of time.  And that makes sense – there is a bunch of overhead to take an argument from Tableau that has to be computed, push it through a Python server, and wait for a result to return.

This is a beautifully simple integration and demonstration of how to take the mantra of seeing and understanding data to the physical world.  With a few lines of code data-driven performance can exist in the physical world.

The Remaining 25 Weeks of #WorkoutWednesday

Back in July I wrote the first half of this blog post – it was about the first 27 weeks of #WorkoutWednesday.  The important parts to remember (if the read is too long) are that I made a commitment to follow through and complete every #MakeoverMonday and #WorkoutWednesday in 2017.  The reason was pretty straightforward – I wanted a constructive way to challenge myself and tangible, realistic goals.

Now that we’re 3 days into 2018 – it’s the perfect opportunity to go through the same process of sharing the impact each workout (the remaining 25) has had on me.

Week 28 | Insights & Annotations
The focus on this workout was adding context/insights/summarization to existing visualizations.  Something that is often asked of those creating dashboards or presenting data.  I enjoyed this workout tremendously because it was a great example of using a feature within Tableau in a way I hadn’t thought about.  The premise is pretty simple – allow users the ability to input insights/findings and customize the summary output.  Very clever use that is a great way to provide a dashboard to someone who has little time or is skeptical of self-service analytics.

Week 29 | Who sits where at the Data School?
I hated this workout – because of donut charts.  Donut charts in Tableau are the level one “look how cool I am” or “I think I’m pretty awesome with the tool” things that people make.  Yes – they are cuter than pie charts (which I don’t mind) – but I strongly hate how these are implemented in Tableau.  Pushing aside my dissatisfaction for donuts – I veered off requirements for this dashboard.  I particularly changed from the original build results because of how seat favorites were computed – more specifically I ended up with more “No Favorite” than the original.  The great points about this workout – the sophistication required to calculate several of the numbers shown.

Week 30 | Loads of LODs
As described this workout had a few LODs.  The most distinct thing I remember about this build relates to the region filter.  You need to decide early on how you’re going to implement this.  I believe Emma used a parameter where I used a filter.  The choice made here will have consequences on the visualizations and is a great way to start understanding the order of operations within Tableau.

Week 31 | The Timing of Baby Making
Ah – a favorite visualization of mine, the step chart/plot.  The two major gotcha moments here are: implementing the step chart and the dropdowns/filters to set highlighting.  This one is solvable in multiple ways and I went the Joe Mako route of unioning my data set.  During the build this seemed like the easier solution to me, but it does have implications later on.  I believe it’s worth the effort to go this route – you will learn a lot about how unioning your data on itself can be useful.

Week 32 | Continuous Dates are Tricky
This is classic Emma style – particularly down to the requirement of a dynamic title that would update based on newer data availability.  The premise itself is pretty straightforward – how can you plot a continuous month line, but have years broken out.  By definition that concept should break your brain a bit, because continuous means it’s the entire date, so how are you plotting different years on a continuous axis?  And hence the challenge of the workout!

Week 33 | How Have Home Prices Changed?
Andy mentioned that this visualization was inspired by Curtis Harris.  And as I write this – it is probably my top visualization in terms of design from the 2017 #WorkoutWednesday collection.  Something about the strategic use of capitalization in conjunction to the color choices resonated with me and has left a lasting impact on my viz style.  This is a stunningly beautiful example of making Tableau a very self-service analytics tool, having dense data, but still being deceptively simple and clean from a design perspective.  Plus you’re practicing dynamic titles again – which I find to be a requirement for most serious builds.

Week 34 | Disney’s Domination
This workout was my first waffle chart.  I’d successfully avoided the waffle (as a general rule I don’t like high carbohydrate visualizations) up to this point.  More than the waffle – was the requirement to data blend.  I’m not a big data blending fan because it is very easy for things to go sideways.  However – the icky feeling you get from data blending is exactly why this is a great exercise to work through.  And also because I believe I did this entire visualization (up to a point) using LODs and had to switch to table calculations.  I learned how to make an LOD into a table calculation (probably the reverse practice for more tenured Tableau folks).

Week 35 | Average Latitude of Solar Eclipses by Century
This is another visualization with design that I find very pleasing – particularly the use of reference lines.  I strongly remember learning so much about the Path shelf this week.  Specifically how to use path to your advantage.  You don’t often see people create something that would otherwise be a line chart, but instead has vertical bars/gantts/lines to focus the eye vertically and then across.  A great exercise and thought-starter on additional visualizations to make.

Week 36 | Which UK Airport Should You Fly From?
This workout is the perfect hands-on exercise on taking continuous sizing on bar charts offered up as a new feature in the past year.  Beyond knowing that you CAN do something, knowing HOW to build that something is the key (at least for me) to be able to iterate and ideate.  This one is more complex than it seems at first glance.

Week 37 | Killings of Blacks by Whites Are Far More Likely to Be Ruled ‘Justifiable’
A viz I don’t want to remember – this one is 100% about formatting.  It took me a considerable chunk of time to complete.  And probably more maddening – this viz needs to be built in one session, otherwise you’ll forget all the intricate details required to make it look “just so.”  I was cursing my PC the entire build – and worse than that I think I restarted it at a certain point because things weren’t lining up how I wanted.  Only complete if you enjoy being tormented.  The upside?  Going through this workout will make you intimately aware of all the gaps and limitations Tableau has as it relates designing.  Also – this was done before changing padding was a feature.  Thanks guys.

Week 38 | (It Takes) All Sorts
This is another “looks simple” but has “some tricks” workout.  I remember someone at our user group asking about this over the summer and if I knew how to build it.  I didn’t have an answer readily available within 30 seconds, so I knew there was more going on.  I highly encourage this build because it demonstrates how sorting works and how multiple sorts interact with each other.  Also – I think whatever sorting I ended up was some sort of mathematical manipulation on my part.

Week 39 | Are the contributions of top sellers increasing throughout the year?
Another trellis chart!   More than the trellis – check out what is being analyzed.  This isn’t superficial or first-pass reading of data.  This is second and third level thought on finding deeper insights and answers to questions within a data set.  So naturally it requires more layers of calculations to resolve.  And of course – the “just so” placement of the far right label.  This is a perfect example of taking a question and turning it into a visualization that shows the answer.

Week 40 | All Sorts Part 2
As advertised and named – the second half of Emma’s sorting workout.  This may actually have been the dashboard where I did some mathematical magic to require the first position to be first and retain any additional sorting.  Also – the devil is in the details.  When you change sort order, notice that the bottom visualization always changes to be the subcategory chosen.  Sounds easy, but takes some thought to implement.

Week 41 | State to City Drill Down
As I look back on my tracker – I realize that I did 38 through 41 in the same day.  And naturally I approach backlog in the fashion of oldest gets built first.  So this was the 4th on a particular day – but I championed this guy hardcore.  I will say it again – YOU NEED TO DO THIS WORKOUT.  The concepts on execution here are next level.  I know it sounds kind of trivial – but it will help unlock your mind to the possibilities of using filters and the art of the possible.  Plus this is a question that ALWAYS gets asked.  “Can I click a state and have it automatically change the view to cities.”  This does that.  Also – this build took me 30 minutes tops.

Week 42 | Market Basket Analysis
I won’t forget this workout anytime soon because it required the legacy JET connector and thinking about how data gets joined back on itself.  This type of analysis is something people often want to have done – so knowing the steps on creation using an Excel data source (or other sources for that matter) makes this guy worth the build.  Follow Emma’s advice closely.

Week 43 | The Seasonality of Superstore
A great viz once again demonstrating how powerful parameters can be – how you can use them in multiple places – and also things you can do to make visualizations more user/reader friendly.  You’re definitely using a table calculation somewhere in here – and you definitely will get angry when trying to recreate the smoothing (particularly dealing with endpoints of the chosen time range).

Week 44 | Customer Cohorts
When dealing with cohort analysis you’re very likely to encounter LODs – that’s par for the course for this workout.  But again – Emma is so clever at taking something that seems straightforward and challenging you to implement.  If you look closely you’ll have to dynamically change the bottom visualization based on where a user clicks.  I remember spending the majority of my time on the dynamic title.

 

Week 45 | Stock Portfolio
This is one sheet.  Just remember that – everything is on one sheet.  And more than that – think about how this is implemented from a numerical perspective – there’s some serious normalization going on to make things show up in context to one another.  If you’re not a math lover – this will be a great way to play with numbers and have them bend to your advantage.  Also – I remember being annoyed because one of the stocks had a maximum value greater than the recorded max (which is it’s own measure) – and that irritated me.

Week 46 | Top N Customers
Think of this as a different way of implementing sets.  It has a lot of similar functionality between IN/OUT and showing members of a set.  And also there are some key takeaways in terms of aggregating dimensions.  Not super flashy on design, but very useful in terms of implementation.

Week 47 | Fun with Formatting
Another visualization where you’re required to do everything in a single sheet.  This will put all that table calculation sweat to action.  I really enjoyed this one.  There is something very satisfying about ranking/indexing things multiple ways in one view.  Also it uses the Caption guys.

Week 48 | Treemap Drilldown
Same concept as week 41, but executed as a treemap.  I think I even opened up week 41 to use as influence on where to go.  Same concepts are repeated, but in a different format.  The automagic of this one doesn’t get old – also carefully look at how things are sorted.

Week 49 | Position of Letter Occurrences in Baby Names
When you say out loud what you’re trying to do – particularly “find the nth occurrence” of a specific letter (we can generalize as substring) in a specific string – it sounds really really hard.  But guess what – there’s a built in function!  The fact that it’s built in made this visualization super straightforward to complete.  You should build this to introduce yourself to a function you’ve probably never used before.

Week 50 | Rocket ship Chart
I very much enjoy this type of chart from an analytical perspective.  It’s a great way to normalize things that are bound to time.  You see immediate inferred rank and results.  Emma put in some requirements to ensure that as data changed this chart would stay accurate.

Week 51 | State by State Profit Ratio
If you want several of the lessons Andy built into multiple workouts all in one place – this workout is for you.  It’s got so many classic Kriebel “gotcha” moments in it.  As I was building this I really felt like it was a specially designed final to test what I’ve learned.  Also this is probably my first tilemap (unless we made one in another workout).  I don’t use them often – so it’s a great refresher on how to implement.  And also – you get to use a join calculation.

Week 52 | UK’s Favourite Christmas Chocolates
When I was building this one someone asked me why I was making it – specifically where was the challenge.  I explained that it was all in one sheet opposed to 4 different sheets.  A natural next question occurred which was “why would you want to do it in one sheet.”  I though that was a very interesting question and one that I explained by saying that for me personally knowing multiple ways to do things is important.  And more specifically as I know to be true of these types of builds – if you can do it in one sheet, it shows a level of mastery on making Tableau do exactly what you want (which is LOTS of things).

And that wraps up 2017 beautifully.  Comparing the retrospective of this half of the year vs. the first half – there are stark differences from my perspective.  I can honestly say that each build got easier as time went on.  Once I got to the last few challenges – I was timing completion to be about 20 minutes.  Contrast that with the first few weeks where I spent hours (over multiple sessions) making my way through each build.

Beyond building out my portfolio, having concrete examples of specific types of analysis, and fulfilling my own goals – #WorkoutWednesday has given me such depth of knowledge in Tableau that it’s ridiculous.  You know how people say things like “that’s just funny Tableau behavior” – well I can (for the most part) now verbally articulate what and why that funny behavior is.  And more than that – I know how to maximize the behavior which is really working as designed and use it to my own advantage.

The last part of this blog series is going to be a ranking of each workout – aimed to help those who are interesting in completing the challenges approach these without getting too discouraged or burnt out on some of the builds that are still (to this day) hard.  Be on the lookout.