When I first started doing what it is I do, I started with the hammer of all tools: Excel. I learned about pivot tables, INDEX, ROW, LOOKUPs and all the tricks you do. I mastered the art of creating a sundry of pivots just so I could pull them into a “dashboard” via GETPIVOTDATA and change them on a whim by selecting a new year from a drop-down. Mind you, I didn’t get my degree in this stuff: Bill Hannan spent more time than he probably cares to recount teaching me. But, I figured it out and Excel became my hammer that I used to
quickly bash down all the loose-laying nails.
Then, we looked at this new tool, Tableau, which can only be compared to a screwdriver. Now, you can bash down nails with a screwdriver, but the whole point is NOT to bash down nails. The point, instead, is to be able to tear down, and reassemble as good as new without breaking things. What the EX**L was I going do with this?
Here’s the skinny on Excel:
This simple table shows sales pivoted by month and year. It’s created, stored, and viewed within the same table.
This is the most basic way we interact with Excel. We create the table we have in mind in the format in which it will be read and used. Period. Full stop. Done.
Moving beyond the basics, the next level of analysis interacts with flat data and turns it into a pivot. This flat data likely comes from a database (sorry, ma, we’re not talking cubes today).
When using Excel, we may condition the data with some row level calculations, such as IF and TEXT to create the facets we desire (MORE POWER!). From there, we either aggregate into a separate sheet via LOOKUP, SUMIF, or pivot tables (and GETPIVOTDATA if we want EVEN MORE POWER) to make the same table as above from flat data.
At some point, our users want more. They ask deeper questions. And it’s up to us to find something with MORE POWER!
More power means change. It’s taking something unconventional, like parts to a vacuum and infusing it with something else to become a blow torch. We’re not playing small time: this is real HOME IMPROVEMENT!
I’ve connected to the pivoted table I used in my above example. I did spare myself a couple of headaches and removed the totals. Now, depending on data, if I’m in for more pain, less gain, I’d hit Sheet 1 and get vizzy with it. But, even Tableau notices something isn’t right.
We’re here to get FIT, not hit, so part 2:
What the EX**L is this FIT?
We’re knocking down the pivot to attempt to make the data as flat as possible. Once reshaped, our data looks a bit more like low-lying data we’ve seen in the past. Tableau loves data when it’s like this.
Select the things with numbers and right-click (or Control click if rocking a Mac like me). Hit Pivot, rename, and cheer.
The pinnacle moment when you hit Sheet 1 looks like this:
We’ve now reached the mid-point in the episode. Writers of comedy call this ‘conflict’. At this point, Tim surmised the problem relates to a lack of power, Al already tried to dissuade him, and Jill is calling motels because the power is due out soon or roof-collapse is imminent. We can certainly recreate the wheel, but that’s something Tim would never do. Al, maybe…but we’re with Tim today.
When Tim focuses on a problem, he looks beyond What IS, and moves to What IF. He thinks nothing off putting fire through a vacuum, throwing car motors on a mower, or adding saw blades to something with a rotor. He suspends sharp adherences to the rules and decides to play. Guess what, kids? The playground is yours.
So, how the EX**L do you learn Tableau?
- Clean your data. Make it nice and pretty.
- Close ALL Excel charts and put all printed copies in a drawer or circular filing.
- Drag pills on the canvas.
- Like what you see? DUPLICATE. Add another pill or replace some.
- Don’t like it? MAKE A NEW SHEET, but DON’T DELETE.
- Repeat step 3 over and over until you have at least 50 charts. Running out of ideas? Make 5 different charts for the same data. No, really, try a line, a bar, a Gantt, or even a bullet chart. There are no right or wrong answers right now, just a journey.
- Once you have 50+, click the dashboard icon.
- Look at charts until you find something that makes you ask a question or answers a question you have.
- Name the dashboard after your question.
- Find relevant dashboards that tie into that question. Missing some? Make some more! There’s a tricky balance between too little and too much on a dashboard and every Tableau user will tell you something different. For today, just get vizzy with it.
- Spend time formatting it.
How do you get better?