There’s a 2011 movie that combines my favorite things: sports and analytics. Moneyball features Brad Pitt and the guy that tried to buy goldfish boots in 40-year-old virgin try to get the Oakland Athletics to the upper echelon of major league baseball by worrying less about the fluffy analytics and focusing in on only one key metric.
The Athletics have a major problem: they are operating with a fraction of the budget of their competition. They are a small market team, the mom and pop of the MLB. Every time they develop a good player the Yankees, the Red Sox or any of the big box baseball teams open up their wallets and buy the player out from under them.
So how does small business baseball try to compete? By narrowing the blinders and focusing exclusively on what wins games. Baseball comes with a nice grab bag of statistics, with fancy names and elaborate meanings. But the As find value by cutting through the noise and focusing on just one metric, one thing, that wins ball games: getting on base.
Players getting on base leads to scoring. Scoring leads to wins. And while all of the other metrics have a home somewhere, the team focused their efforts on getting on base as often and cheaply as possible.
They allocated their limited budget specifically to this cause.
And so, I use this elaborate metaphor to challenge you. Right now, while money is tight and cash flow is everything, what is your metric? What is the single thing, that you can control, that will most tangibly lead to winning?
Virtual networking is great, but does it generate orders? Can you tie your retweets to your gross margin?
If we think like the Oakland As we need to ask ourselves what is going to lead to the most sales. As a restaurant, maybe it’s going to be views of your online menu. So what can you do to maximize your budget and get those menu views?
How are you going to get on base?
I've been obsessed with building a Calendar in Tableau. I've tried more than once to sneak it into a client's dashboard, but it's reception rarely matches my enthusiasm for it.
Today, amidst some tremendous procrastination, I've finally built myself a top tier interactive Calendar. In my opinion, anyway.
Tableau public link at the bottom.
Who is this view for? I think the best example, which I don't have great data for, is for future planning. I had an event planning client who could see upcoming room capacity by day. Or shift scheduling for a workforce. But the Calendar works well enough for past results too. I've done hours worked by day. You could also do Revenue, Net, Orders, Customers. Any of your KPIs, really, but you'd want something that occurs Daily. New Hires, for example, might be a bad fit. Anyway. I digress. Let's look at a picture.
Staging the Data
This is a view of every day this year, and how many hours I've worked on that day. It's also showing future dates with no hours. That's one of the first steps to setting up this type of view. You TYPICALLY need a second table. It's just one column, and it's every day in the calendar time frame. I have an excel sheet that runs from Jan 1 2017 to Dec 31 2021. Every day needs to be represented, past and future. This is then right joined to your data on the date in your dataset. If you don't do this, your calendar will be missing days where no activity occurs in your business. Some databases might have a record regardless of activity, but that's certainly the exception not the rule.
Building the sheets
I'll go over the sheets themselves. I've build a single sheet and once happy with it duplicated it 3 times, and switched the months.
1 - The columns. These are straight forward. The only thing worth calling out is to make sure you use the Date Field from your custom table that has every date. Otherwise you'll be missing the data on days that don't exist in your database. To get WEEKDAY you right click on the month dimension pill, go to 'More' and select Weekday. I then did a bit of formatting on my headers.
2- Filters. The Month filter just narrows down how many Months I show on a sheet. I originally went with Quarters but if you are anti scrolling, as I am, four wide/three long does better than three by four. So I scrapped quarters and hard filtered to four month intervals.
Only One Year - this is tied to a yearly parameter that lets the user switch. In hindsight a single value select filter on Year(EveryDay) would have been fine.
3 - Marks. The colour is a calc field. You could easily just prop your measure on there but I wanted a bit more control so I created a calc field that lets me hardcode my colour steps. It also allows me to switch measures using a second parameter.
Day(EveryDay) is on the text mark and is formatted to be top right. This gives the calendar look of having only the day in each box.
The rest of this stuff is for the tool tip. You can add the measure into the text box, bottom left. It's a good look, but I found it a bit too crowded and took it out. If you don't mind the scroll then this is a good tradeoff.
4- Rows: Week in month is actually a slightly complex calc to give you how many weeks are in each month. I stole it from the forums.
IF DATEPART("weekday", DATETRUNC("month", [EveryDay])) = 1
ELSE INT((DATEPART('day',[EveryDay])-DATEPART('weekday',[EveryDay])+7)/7)+1 END
So after duplicating the sheet 3x and fixing the filter to bring different months on each, add all the sheets to the dashboard. The only stickhandling I did here was use a vertical layout container and select "distribute evenly" to make sure the charts all occupied the same amount of space. Then add the parameters in. One toggles measures from clients to hours, the other changes the year.
I've also add a viz in tooltip to show year over year per day. Actually pretty happy with this one. I think it looks great and gives a bit more functionality. On my internal dashboard I've added a second tooltip that shows all the hours I did on that day, and who was billed what.
Tablleau Public URL
Here are a few facts about yours truly
Roughly 10% of all men are colour blind, which somehow equates to about 50% of commentors on my public work. The colours most impacted by colour blindness are red and green, which can wreak havoc for any KPI analysis. To help see that havoc, here is a look at Christmas songs that have made the billboard top 100 since 1958
And here's what this looks like to a person who has protanopia color blindness (typically the most acute variety (shout-out to David for the translation))
Not exactly festive. But most notably, take in how impossible it is to distinguish the red from the green. If this were a KPI chart, you'd get nowhere fast.
Alas, here are some tips for making a colour blind approachable dashboard.
It's lost some festivity, sure, but it's also gotten to be a bit more palatable (sic.) to the 10% of men who we're having no fun earlier.
Hi really don't like Halloween and I really don't like video content, so I thought for today I'd do both. Here are 21 Tableau Tricks and Treats ranging from beginner to maybe Intermediate +
1. Right click on the marks, rows or columns shelf to create a calculated field that exists only in that worksheet 0m20s
2. Create information icons with explanation in the hover help guide the user by creating sheets with only an icon or an image, and then tool (show dashboard) 0m40s
3. Right click on a field and click default properties > number format and you can set to a currency or percentage so you don't have to change it every time you use it in a view. You can set default aggregations here as well. 1m16s
4. Right click and drag fields onto your view to pre-determine how they display (oops, didn't get on video, use your imagination)
5. Copy portions of a calculated field and drag them onto the view (great for troubleshooting) 1m50s
6. Hold Ctrl and drag a calculation from the pill to anywhere else to copy it - including into your field list to create a new calc field. 2m25s
7. If you're using measure values on colour, right click the measure values pill and select individual legends per measure. This lets you set a different legend for each measure. But it can get psychedelic so be careful. 2m48s
8. In a legend, double click a colour to go to the customization GUI 3m00s
9. To reduce the colour noise, you can use advanced customization to reduce the impact of outliers and stepped legends that reduce the number of total colours. 3m41s
10. In the toolbar, click analysis, view data. It will show you all of the data in the current worksheet. A quicker way than highlighting all the points and right clicking view data. 4m08s
11. If you need to export a measure name by measure values table, it will export as a cross tab and not a table (not like it looks in Tableau). To alleviate this, multi select the measures (ctrl + click) and drag them onto the rows. Then multi select again, right click and select dimension. Now you'll see that the export formats as a table. 4m23s
12. Drag the middle of a range filter to maintain the range but change the end points. 4m48s
13. Have dynamic colouring in text or tooltip by creating multiple if statement calculated fields, that return the desired value, one for "bad" and one for "good" each without an else statement. Set them each in the tool tip, colour the bad one Red and the good one Green. Only one will ever show up at a time. 5m25s
14. Right click on a date, format and scroll down to custom and pick the perfect date format. 6m32s
15. Use map layers to add street or satellite details to a map. 6m51s
16. To dynamically highlight only one dimension from a group, create a parameter that has all of the dimension options and then a calculated field of Parameter = Dimension. Set that field on colour, and click a bright colour for true and a bland colour for false. Order the legend so that true is on top.7m24s
17. You can set a password on a dashboard using a parameter and a calculated field the verifies the Parameter is set to a specific word. 8m34s
18. Use a Count Distinct of dimensions in a view to swap create a dynamic label that shows either a specific item or lexicon for multiple items, based on filters. IF the count distinct is equal to 1 THEN use Attrtibute on the dimension to show the specific dimension, ELSE show "All Products/Teams/Cities" 9m53s
19. On Startup hover across recent files to preview contents by worksheet 10m58s
20. Ctrl and click multiple worksheets and right click, select copy. You can and paste these worksheets within a workbook to duplicate, or from one workbook to another. It will even bring the datasource with it. 11m21s
21. The 21st trick is that there isn't a 21st trick.
I'm working on a hockey stats portal that you can find here - If you have any requests, feel free to give me a shout by any means necessary.
One of the things I love about my job is I don't fully know, day to day, what I will get up to. The data life cycle is so wide and varied that every journey is unique.
For the last year or so, I've been using a tool called RescueTime to track and categorize all of my activity. It really helps me understand what, exactly, a business strategist does.
1. Tableau - by far my most used program. I spend a ton of time in Tableau simply because it is the absolute best way to communicate data to my clients. This is the front-end tool that turns data into decisions.
2. Gmail - a sad truth about any consulting is the amount of time spent communicating with clients through email. 6% of my time is spent crafting and reading emails.
3. MS Excel - Encouraging my clients to get out of excel and into more robust data bases is the drum I can't stop beating. But unfortunately excel remains a vital part of many organization's data cycle.
4. DBeaver - DBeaver is the ultimate database management tool. It has connections to a large number of major database platforms and let's you easily switch between connections. Data strategy requires a lot of data discovery, and DBeaver let's me do that with the fewest barriers.
5. PBIDesktop - I stand by Tableau being the best data viz tool, but if you're an org that already has heavy ties into Microsoft or you're looking for something a little bit cheaper than Tableau, Power BI is a fine option. Some of my clients have taken this route, and I've developed their PBI footprint with them.
Here's the full list and time spent on all programs from January 1 to July 31 2019.
A growing fad I've seen on the interwebs is creating Resume's in non-traditional software. Whether it be a personal reactive Webpage, or an origami bird, people are eager to distinguish themselves via the delivery of their resume. And fair enough, with how competitive the hiring market is, that could be the requirement to get the attention of an inundated hiring team.
More personally, it's people releasing Tableau based Resumes. Showcasing their professional achievements through elaborate charts. Honestly, this is a domain where I swear a PDF file with words is going to work better.
But not wanting to be left behind, nor to let me grouchy self get in the way of good ole fashioned progress, I put together my Tableau resume. The hardest part is not inundating it with words. I've put most of the pertinent information in hover overs, but feel like I've left enough top line information that this is consumeable without Tableau.
But I'll let you be the judges. Here is the URL, with the JPeg below.
A right to Canadian passage is being able to complain about the snow winter after winter. It's also customary to confidently announce to anyone who will listen that "this is the most snow we've had in a long time". But how short our memories are. Slightly annoyed with the rash claims made by friends and neighbors, I again turned to data viz.
Environment Canada does a great job at keeping detailed weather files from most Canadian cities. Unfortunately the level of detail and measurements documented vary by polling station. I had plans of expanding my analysis across Canada but it was time consuming to find formats that would cooperate.
I also doubt the accuracy of some of the reporting. Especially as I was tracking this live and comparing to my front lawn. But I'm sure there is some system in place.
The biggest challenge with this piece was creating a static way to show 12 years of data. I'm into making tool agnostic reports lately that can be widely consumed. It's a learning curve but it's a good direction to head. The balance of putting not too much nor too little information is tricky.
But, with that said, no tableau link for this one as I got to all fit in a nice Jpeg. Here is a look at 12 years of Fredericton Winters.