January 2020 Sports Viz Sunday

The topic for the first #SportsVizSunday of 2020 is personal health data. I took some leeway with the topic and looked at my golf handicap index and scores. I normally walk the golf course and golf impacts my mental health (sometimes positive and sometimes negative). There were a few times this year where I thought about buying a boat.

For #SportsVizSunday, I wanted to look at where my index fell in relation to other women who keep a handicap and highlight the scores that count towards my current index. As with most public work I do, I like to keep it simple. I spend a lot of time during the week working on dashboards so in my free time I tend to keep it light and simple.

The 2019 season was a bit all over the place for me. I struggled with my irons for the last two seasons and that definitely impacted my score. While that aspect was off the rest of my game was in good shape and that helped me get my handicap index down to an 18.4.

I play most of my golf at two different courses and wanted to see what my score and differentials looked like at those two courses. I felt like I played better at Furnace Brook because I hit my fairway woods and hybrid more than I hit my irons. The data backed that up. I scored better (based on differential) at Furnace Brook than at William J Devine.

my differential at the Brook was 4 strokes lower than at the Park

In 2020 I’m going to track more of my golf stats and visualize them to see where I can get better. I know where I struggle with my game, but, seeing the data makes it a bit more real.

#PreppinData Week 1 2020

The first #PreppinData challenge for 2020 was to recreate Ryan Sleeper’s financial statement table using data from the fictitious Chin & Beard Suds Co.

To do this we need to:

  • get the profit totals
  • get the profit subtotals
  • update the blanks with the total & subtotals
  • indent the 2nd & 3rd level data

This is the initial view of the data. Based on the naming convention of the Item my first thought was to split off the number portion of the field and use that as a way to create a hierarchy.

PreppinDataWeek12020S_1

I used the custom split tool to parse the field off a space and took the first field. I trimmed any extra spaces and renamed this field Item Id. These are the calculations:

PreppinDataWeek12020S_2

I then created by hierarchy levels taking the left X number of characters from the new item id field. My thought was I would use these to get the totals & subtotals. These are the fields I created for the hierarchy:

PreppinDataWeek12020S_3

I also created a new field with the indented item names:

Format Item Name

IF LEN([Item ID]) = 2 THEN [Item]
ELSEIF LEN([Item ID]) = 3 THEN SPACE(5) + [Item]
ELSEIF LEN([Item ID]) = 5 THEN SPACE(10) + [Item]
END

 

After I had my levels I then created two aggregates to get the totals and subtotals. The first one sums the profit by my new top level field and the second one sums the profit by the new second level field. I joined both of these aggregates back to the prior step where the top level in to totals aggregate = the item id & where the second level = the second level.

PreppinDataWeek12020S_5.PNG

The last step is the clean up step. I this step I have 11 changes to the joined data.

  • remove the duplicated fields from the joins.
  • merge the profit field from the initial step with the profits from the aggregates
  • rename the merged profit fields to profit
  • created a calculated field to get the length of the item id field to sort my rows correctly
  • renamed the Format Item Name field to item name
  • removed any remaining unnecessary fields

PreppinDataWeek12020

This was a great challenge to kick off the 2020 #PreppinData series. I love the formatting idea from Ryan and have a few ideas of how I can implement both Ryan’s table and this PreppinData challenge in my day to day work.

If anyone is interested in getting a copy of my flow please let me know. I am more than happy to share my approach.

Happy Preppin!

 

 

 

 

 

 

 

 

 

 

 

 

The Tableau Community

A quick sidebar with Mark Edwards and a message from Adam Mico on Twitter on the last day of the Tableau conference got me thinking about defining the “Tableau community” and what my Tableau community is. I’ve been noodling this for a few days now and this is what it means to me.

My Tableau community is:
Krish who works at TD bank in Toronto and attended his first Tableau conference. I was sitting at a table with some folks I “knew” from Twitter at the TUG Tips Battle Session and noticed that someone was sitting by themselves at a table. I have been that person at many events. I moved to the table, introduced myself and struck up a conversation. There are so many people like Krish who are in the Tableau community but not involved in the community projects.

My Tableau community is:
A man I had lunch with on Thursday when we arrived at the same time to a 2-seat table to eat our lunch. I spent 5 minutes with him and learned that he was new to Tableau and trying to learn as much as he could at conference. I suggested that he check out Ryan Sleeper’s blog and Playfair Data TV as resources to get him up to speed on Tableau when he got home.
A few hours later I ran into Ryan at the community area of the Data Village and ended up getting pizza with him, Sean Miller, Tom O’Hara, Will Strousse and Furkan Celik. I know Tom and Will and enjoyed getting to know Furkan, Ryan, and Sean more. Ryan even schooled the 3 Bostonians there on the time out chair at Pizzeria Regina.

My Tableau community is:
James, Simon, and Spencer who have given me exposure to the larger Tableau community by having me host #SportsVizSunday twice and by asking me to be on the half time panel of their data19 session. Giving people an opportunity to get their name out there is invaluable and I appreciate what they have done for me.

My Tableau community is:
Bridget Cogley who told me things I needed to hear and encouraged me not to settle. Don’t let the shortness of this section fool you. This was one of the most important conversations I had all week.

My Tableau community is:
All of the people I know from the Twitter community. All of the people I know from BTUG including my first TUG friends Paula Munoz and Susan Glass. All of my co-workers who use Tableau including Amar, Jesse, Josh, and Tom. All the people who have asked and answered questions on the forums that have helped me. All of the people who write blogs and do videos to share their knowledge.

The community isn’t just those with rocks, those that are ambassadors, those that are on Twitter, those involved in the community projects, and those that win community awards. The Tableau community is anyone who uses Tableau in some capacity and I can’t wait to meet more and more of those people.

Preppin Data Week 14

The #Preppin Data week 14 challenge asked us to determine the impact of implementing a meal deal at Whyte’s Cafe. I thought this was a good challenge and per usual I broke the steps out into a number of steps. Seeing the solutions for the weekly challenges made me realize that I usually have a different approach than the solution and that I like to break things out into multiple steps and this week was no different.

Here is what my final flow looks like:

PreppinWeek14

I connected to the .csv file and inserted a clean step. The steps say to replace null prices to 1.5, however, I didn’t have any nulls when I imported my data so I skipped that step. They also stated to replace the null member ids with a zero. This is the only action in my first step. To do this I right clicked on the null in the member id field and selected edit value and replaced the null with 0.

The output needed to have the total ticket price, the number of meal deals, the total prices for items not included in the meal deal, and the difference between the total price and the meal deal adjusted price. I determined these through 3 different branches.

The first aggregate I created was to determine the number of meal deals per ticket. To get this I needed to know the number of items per ticket.

Week14ItemAgg

After the aggregate I added a pivot step to move the type from rows to columns. I did this so I could use the columns to determine the number of meal deals per ticket.

Week14ItemPivot

I then inserted a clean step to create the meal deal flag and the number of meal deals per ticket. This is my approach:

  • determine if the ticket has items that are meal deal eligible
    • [Drink] > 0 AND  [Snack] > 0 AND  [Main] > 0
  • right clicked on the new meal deal eligible field and kept true
  • determine number of meal deals per ticket:
    • IF [Drink] <= [Snack] AND [Drink] <= [Main] THEN [Drink] ELSEIF [Snack] <= [Drink] AND [Snack] <= [Main] THEN [Snack] ELSEIF [Main] <= [Drink] AND [Main] <= [Snack] THEN [Main] END

The next step was to get the average cost per item and ticket. I created this to use to determine the excess cost later in the flow. Because drinks, mains, and snacks all have different prices I used the average by type to get the excess cost.

Week14ItemAvgCost

I also pivoted the types to columns and then added a clean step. In the clean step I replaced any null values to a zero.

The third aggregate totaled the cost per ticket.

Week14TicketTotal

I added a clean step after the aggregate to round the total cost to 2 decimals. I used this calculation ROUND([Total Ticket Price],2).

After the aggregates were ready to go I joined the 3 branches together and started the final steps. These are the calculations I did in the costs step:

  • determines meal deal cost:
    • Meal Deal Total: [Min Meal Deal Item] *5
  • determine number of items that aren’t part of the meal deal (3 different calculations):
    • [Drink_Items] – [Min Meal Deal Item]
    • [Snack_Items] – [Min Meal Deal Item]
    • [Main_Items] – [Min Meal Deal Item]
  • determine cost of excess items:
    • [Excess Drink Items] * [Drink_Price]
    • [Excess Snack Items] * [Snack_Price]
    • [Excess Main Items] * [Main_Price]
  • determine excess total:
    • ROUND([Excess Drink Cost] + [Excess Main Cost] + [Excess Snack Cost],2)

Because I like to break things up I inserted another clean step. These are the calculations I used to get the final output.

  • determine the new total price using the meal deal:
    • [Meal Deal Total] + [Excess Total]
  • determine the cost difference:
    • [Total Ticket Price] – [Total Ticket Meal Deal Adj]

My meal deal items and costs matched the solution output but my prices do not and I haven’t been able to figure that out. I have done pivots in excel to get the cost by ticket and they match what I have in my output. I decided to not get too hung up on that an use this week as a concept and not exact match.

I enjoyed this week’s challenge and I continue to enjoy seeing the different approaches to the challenge each week.

 

Different Ways of Getting the Same Result

There are usually a number of different ways to get to the same result in Tableau & Tableau Prep. Week 9 of #PreppinData is another example of this.

For this edition of #PreppinData we looked at Chin & Beard Suds Co’s Twitter complaints. We were given a list of complaints and asked to:

Remove Chin & Beard Suds Co Twitter handle
Split the tweets up in to individual words
Pivot the words so we get just one column of each word used in the tweets
Remove the 250 most common words in the English language (sourced from here for you: http://www.anglik.net/english250.htm)
Output a list of words used alongside the original tweet so we know the context for how the word was used.

Here’s the flow I created

Week9

The first clean step splits the text on the space character. The next step pivots all of those splits back together in one column. In this pivot I used a wildcard pivot where the field name contained Tweet –

week9c

After consolidating the splits, I did a few clean steps to get the two sources ready for joining. Anytime that I join on text I always make sure to trim all the extra spaces and make the text fields either upper or lower case. I think this is a good habit to be in for text matching. I also excluded the company Twitter handle and any null records in this step.

week9d  week9e

Now it is time to join them together. The first join I did was an outer join between the tweets and the list of the 250 words. In the step after the join I kept only the records from the 250 list that were null (I used the rank field).

The other way this join can be done is with the left unmatched only join type. When you use this join type all you need to do is remove the two fields from the 250 list.

Initially I didn’t think of the other join type and found a way to get to the final result. Going back and looking at the joins the second option is probably the better way to go. There isn’t a wrong and right way to do it just different approaches.

Happy Preppin!

#PreppinData

I just finished week 2 of the  Preppin Data challenge and wanted to walk through my approach. One of the things I love about Tableau and Tableau Prep is that there are a number of different ways to get at the same result.

This week Carl & Jonathan gave us a file that had a big header, names that needed to be cleaned, and metrics that needed to be moved to columns. The output needed to be 6 columns and 14 rows.

After setting my connection to the file the first thing I did was check the Use Data Interpreter box. This helper removed the unnecessary header at the top of the file.

PreppinWeek2_A
check the data interpreter box to remove the unnecessary header

Whenever I built something in Tableau Prep I like to always add a clean step after my connection to get a sense of what is in the data. When I did this I noticed that my city field had a value called “city”. I knew from looking at the initial file that this was a secondary header so I right clicked on the value of city and selected exclude.

PreppinWeek2_B
remove the secondary header row

At this point I also added an aggregate to see how many rows were in my data set. I like to add these as I build out a flow to get a sense of how my record counts change as I build out different steps.

I added another clean step and I did this because I like to partition out my changes when I build something new (I’m quirky). I could have done these all in the first step. In this step I grouped the various city names by pronunciation This took care of all but two values. I edited the group and manually added “nodonL” to London and “3d!nburgh” to Edinburgh. In this step I also created the new header field which combined the metric and the measure and then removed those fields as they were no longer needed.

PreppinWeek2_C
used pronunciation group by with a manual add, created new field and removed unnecessary fields

The next step was to move the values from the rows to columns. This is done in a pivot step. Most of the people I help with Prep think Pivot = Pivot table and are confused when they add that step. Pivot will reshape your data.  My pivoted field is my new field that I created in the prior step and my field to aggregate is the value field.

PreppinWeek2_D
this moves the data from rows to columns

At this point I also added an aggregate step to make sure I had 14 rows as the instructions called for. This is the full view of my flow.

PreppinWeek2

Thanks for reading and happy preppin!

Fairway Ladies Year in Review

My goal for 2019 is to produce more personal projects on my Tableau Public page than I have in the past. I’m kicking that off by looking at the 2018 golf season for my golf group. The Fairway Ladies of Franklin Park play at the William J Devine golf course in Boston MA. To get a high level summary of what our season looked like I pulled a report from GHIN (the program we use to keep our handicaps).

I designed the dashboard to be a wide layout and kept it with a simple color scheme. I’m pleased with how it turned out. I went back and forth on the score differential chart a number of times and finally settled on the bar code chart.

I’m looking forward to doing more of this in 2019!

Fairway Ladies 2018 Season

2018 Tableau Acknowledgements

To keep with the popular year end theme of year in review here is my list of acknowledgements to the Tableau Community in 2018. These are in no specific order.

Susan Glass (@SusanJG1& Paula Munoz (@paulisDataViz )  – I “met” Susan & Paula via Twitter and then got to meet them in person at the Boston Tableau User group this year. I’ve been a sporadic BTUG attendee for years but never really met anyone at these user groups. Sometimes the user group meetings felt like riding the T – unless you already knew someone on the train you avoid eye contact with everyone else. It is great to have some real live Tableau friends now.

Tom O’Hara (@taawwmm– Tom is Tableau support at Comcast. The range of questions he answers on our internal Slack & Teams Tableau boards is amazing. He’s always helpful and supportive. I hosted Sports Viz Sunday in September and was thrilled that Tom supported me by entering a viz. It’s great to have work colleagues support your personal Tableau endeavors.

Josh Tapley (@josh_tapley& Corey Jones (@CoreyJ34 ) Josh & Corey run the Philadelphia Tableau User group and gave me my first opportunity to present at a TUG. I did a live demo Tableau Prep and enjoyed presenting more than I thought I would. I was also blown away by Corey at the TUG. There were a number of St. Joes students who presented their work after they were done Corey acknowledged something he liked about each one of their vizes.

Ann Jackson (@AnnUJackson ) & Luke Stanke (@lukestanke ) – Ann & Luke put out my favorite podcast Hashtag Analytics https://bit.ly/2QTiAJW. Their podcasts are great conversations on data and the Tableau community. You’re missing out if you aren’t listening to these.

The SportsVizSunday Guys (@SimonBeaumont04 , @JSBaucke , @sportschord ) – Thank you for asking me to host #SportsVizSunday in September! Being asked to host September’s challenge was big for me. This was the first time I’d been invited to be more involved in a data viz project. There is a big Tableau community on Twitter and at times I’ve felt a little lost because I don’t create flashy work and I don’t have a gazillion followers. When Simon asked me to host I felt great. We all like to be recognized from time to time (even the introverts like this).

Sarah Bartlett (@sarahlovesdata ) – Sarah is the Tableau ambassador on Twitter. No one else in the community welcomes and supports people like Sarah does. She also promotes new folks every week with #Tableauff. She’s also got mad skills and it was awesome to see a women in the IronViz Europe finals this year.

Chantilly Jaggernauth (@chanjagg ) & Amar Donthala (@AmarendranathD )- Chantilly & Amar created a Millennials & Data (millennialsanddata.com ) program this year to prepare millennials to enter the data driven world. This is in addition to their full time jobs at Comcast. Their first cohort of 16 produced amazing work and they all passed their Tableau Desktop Specialist Certifications. I see great things in the future for Chantilly & Amar!

There are a number of other folks who have influenced me in 2018. This list is my no means inclusive of everyone but these are folks that I wanted to highlight.

R&D Makeover Monday

In last week’s Makeover Monday recap Andy reminded us that this is a makeover. The intention is to evaluate what is good and what can be improved with a viz and create a new one with those points in mind. People can use makeover Monday for what they what but the intention is to improve upon the selected viz.

I normally try to take that approach but I don’t often document what I like and what can be improved so for the next few weeks I am going to attempt to put my thoughts and approach together here.

The viz this week comes from HowMuch.net and looks at R&D spending across the globe.

R&amp;D-AROUND-THE-WORLD-22c6

I like that the person who created this tried a different approach to displaying the information. They want the reader to focus on the large circles in the middle for the US, China, Japan, and Germany. What I think can be improved is the amount of clutter in the viz. There is a lot going on here between the circles, the map, the flag, and the multiple colors. I think a better approach would be to simplify the viz and draw the attention to the top 5 countries. I don’t think the flag and the country shape add to the story so I would remove them.

I selected a treemap for this week’s makeover. While treemaps may not always be the best option to compare values I think in this case it works because I want to highlight the contribution of the top 5 countries and I don’t want to compare all of the countries against each other.

RD Spend

Overall I think this meets the goal of drawing attention to the top countries.