Month: July 2020

This week’s challenge from Lorna and Sean was accompanied with a combined Preppin’ Data/Workout Wednesday Live Session. It was great, I got to meet some people I’ve only conversed with on Twitter, and it was nice having Lorna and Sean checking in, which got me through my biggest roadblock. I probably would’ve ended up checking Lorna’s workbook after banging my head against the wall.

This was the first Preppin’ Data I’ve done. I’ve used Prep fairly frequently, so I was able to work through it pretty well. It took some dual joining, manual grouping, pronunciation grouping, calculated fields, and aggregation. Fun stuff!

To start the WOW challenge, I connected the three data sources with noodle relationships. Lorna was kind enough to hint that it worked best to start with the Host Countries source, so I did. I connected Country Medals on Year:

Then I connected Medalists to Country Medals on Year and Country:

The first thing I created was the Medal Score calculation:

Initially when I created the calc, there were only 8 countries in 1896, when there should be 11. That’s when I realized I needed the ZN, since some countries had null values for one or more of the medal types. Next I needed a rank function based on the medal score:

I used RANK_UNIQUE in order to have one country for each place, even if they have the same medal score.

I brought Year from Country Medals to Columns, and put Rank onto Rows. After I tried filtering rank on top 10, to no avail, I played around for a while to get the lines to match up to what Lorna’s had. After a few minutes, Lorna checked in with our group, and had pity on me. She hinted that I needed to make items that weren’t top 10 NULL instead of filtering them out. So I created a calc to do so:

The FLOAT is there because I changed it to a decimal number. Then I added that to Rows, and computed using Country. Then in order to get the lines and dots, I made a dual axis. In order to get 1 at the top, I reversed the axis. And magic, it worked! Next I needed to figure out the hosted and participated to get the stars.

I had to use the ATTR because of an error I was getting that didn’t like something about the relationship setups. Then I changed the second mark type to shape, and added Hosted to Shape and Size. As I started with the color for the stars, I needed to find a way to only get the host countries instead of all the countries:

Then I assigned the Hue color palette (per instructions) and changed the Null to gray. That finishes up the bump chart. Now for the tooltips.

There are two vizzes in the tooltip. The first is total medals for the country by Gold, Silver, and Bronze. So I put those three values into Measure Values, added Measure Values to Columns and Label, and Measure Names to Rows and Color.

The second tooltip shows the Top 10 medalists for the country in that year. So Medal on Columns and Color, Athlete on Rows, COUNTD(Event) also on Columns and Label.

As I set up the tooltip, I needed two more numbers, total countries participating that year, and total medals for the country that year.

Total medals was pretty much like the Medal Score:

Total countries was my one LOD calc in this workout:

Now it’s time to put the tooltip together:

That finishes the bump chart with the tooltips.

For the bottom chart, I needed a calc that checked whether the host country was in the top 10 countries that year.

In order for the Top 10 calc to work, I can’t filter on Hosted, so I had to add Hosted to Rows, and hide ‘participated’. Then I added Host in Top 10 to Shape and Size. Year on Columns, Country in Detail. Again the tooltip needs Medal Score and Total Medals, so I added those. The last thing we need is the viz in tooltip showing the top 10 countries and the host if they’re not in the top 10. Rank and Country on Rows, and SUM(Medal Score) on Columns. I need a filter to get the top 10 and the host:

Added that to filter, and then added Host in Top 10 and Hosted to Color (you have to add the second one to Detail, then click on the Detail icon and change it to Color). Then with different years selected, I set the color for the hosted values. With that, I finished up the Hosts sheet tooltip:

Now that the sheets are done, it’s a pretty easy dashboard layout, with a Vertical container, the title textbox, Bump sheet, Hosts sheet, and footer textbox. In order to get the Hosts items to match up with the Year axis on Bump, I added internal padding of 55 px. And we’re done!

Click to view in Tableau Public

This week’s challenge is a throwback from Emma Whyte to a 2017 WOW challenge. It’s a scatterplot of sales and profit by state, but when you click on a state, the points shift to cities in that state. Emma used set actions, and you would think that would make things fairly easy, but they’re still plenty complex.

I started out with SUM(Profit) on Columns and SUM(Sales) on Rows. I knew I would need a set, so I created a State Set:

Then I spent twenty minutes trying to figure out how to use the set to switch from state to city in the view. After trying several different options, I thought I would check out Lindsey Poulter‘s Set Actions workbook, and it had just the direction I needed.

I started with a Dimension calc, returning the city if there’s a state in the State set, otherwise returning the state:

Next, I created a calc that returns a string of ‘State’ or ‘City’ depending on what the Dimension value is:

Then I created a calc to filter based on the number of distinct Level values:

If there is a state in the State Set, there will be two values in Level, ‘State’ and ‘City’. If there’s not, there will only be one value, ‘State’. So the Fixed LOD of {COUNTD(Level)} will return 1 if no state is selected, and then ‘Keep’ those records with ‘State’ in Level. It will return 2 if one (or more) states have been selected, and then ‘Keep’ those with ‘City’ in Level. When you add that to the Filters, and select Keep, it will filter to the states if no state is selected, or the cities if one is.

So I added Dimension to Detail and Label, and Level and State to Detail. Then I needed to set the color based on Profit:

If the SUM(Profit) fixed at the Dimension level is less than 0, make it red, otherwise blue. Then add that to the color shelf, set the red and blue accordingly, and I ended up setting it about 65% opacity.

Then I needed to touch up the tooltip:

The last thing I needed was the header. I created a calc to give me ‘by State’ or ‘for’ + state depending on the set selection:

And another one for the sub-header:

Then set up the worksheet title:

Toss the worksheet into a dashboard, and we’re finished!

Click to view in Tableau Public

This week’s challenge by Ivett involved dynamic labeling of a heatmap. This week had a basic and advanced challenge. After figuring out the basic version pretty quick, my methods for the solution had to change in order to accomplish the advanced solution.

I started out with MONTH(Order Date) on Columns and YEAR(Order Date) on Rows. Add AVG(Quantity) to Color, and you have a heatmap!  In order to add the totals, you head to the Analysis menu:

When you do that, though, the totals don’t incorporate with the colors right away. But if you click on Color, and Edit Colors…, there’s a check box there to include totals:

Now for the labels. From looking at the workbook, I decided I would need to use a set action for the hover label, and a conditional to get the labels to always show on the totals. Ivett was kind enough to give a hint to use Size(), which was really handy. I right clicked on a cell and Create Set, and created the Label Set which had Month and Year in it. Then I created the Set Action to set the value based on where you hover:

Then I created a calc to display the quantity if the cell is in the Label Set:

I put AVG around the IF statement so I didn’t have to worry about aggregating the Label Set (the ol’ “can’t mix aggregate and non-aggregate” issue)

Next was the overall label that incorporates size. I played around with Size on the label to see how things worked. I found when I calculated size based on Month and Year, it looked like this:

So I used that to create the label calc:

If the size is less than 48 (the totals sections), give me the AVG(Quantity), otherwise return the Individual Label. Add this to label, and Edit table calculation:

I also noted the additional space dividing the totals, so I went to Format Borders, and changed the Grand Total/Pane thickness and color.

That finishes up the Basic challenge.

For the advanced piece, I tried to pick up where I left off with the Basic, but eventually found that I needed to change a few things in the way I set it up. The first thing I did was change the dates in Columns/Rows to custom dates.

This was necessary to get the sets to work the way they needed to. So then I created a set for for the months and years (Month Set and Year Set). I figured I would need a set action to run on Select to get the labels to appear on the whole heatmap.

My previous label calculation using the overall size wasn’t able to work with the additional label requirements upon clicking. But, although I got really close a few times, I just couldn’t quite figure it out. A peek at Ivett’s workbook pointed to looking at size by row and column, instead of overall. So I created a Row Size and Column Size calc with SIZE(). Then when you use those and calculate the Rows – Table(Across) and the Columns – Table(Down), the totals end up with a value of 1. That makes those usable for making the total labels always show.

In order to keep the hover label, I needed a new set with my new date fields (Hover Set) and the accompanying set action.

That gives me what I need to cover the labels from the basic challenge:

The same peek at Ivett’s workbook showed the creation of a Selected Value, which allows it to be used in calculating the difference of each month from the selected value.

Then I can add the selected value and the remaining difference values to the label calc:

If the Month and Year Set are true, return the Selected Value. Otherwise, return the difference between AVG(Quantity) of the cell and the Selected Value.

Now my only problem was getting these values to only show up when I clicked on something. I settled on starting the IF statement with ISNULL(Selected Value), which should only have a value if a cell has been clicked. So that final calc is:

After you add it to Text, you have to edit the table calc to run on Specific Dimensions:

Making sure to also adjust the Column Size calc:

The Color calc was a bit simpler, because I need it to either show the AVG(Quantity) or the difference from the Selected Value:

The last thing I need is the highlighting of the Month and Year values upon selection. Couldn’t figure this one out, either, but it was rather simple. Just needed a calc to identify the Month or Year set:

Then add it to detail (with MAX), and you can add a Highlight Action on the dashboard:

And we’re in business!

Once I finished, I played around with adding indicators like Rosario Gauna did, but was kind of fried by that point to figure out the additional calcs. I bounced around so many things, changing and adjusting to figure things out, I feel like this was all over the place. I hope it was some help.

Click to view in Tableau Public

After 3 weeks of being out of town spending time with family, it’s time to get back to the WOW party. I left off with a challenge from Ann in Week 24, and came right back to another challenge from her this week. As always, much of this was pretty straightforward, and then I continued to find little things that are like, “what????”

I’ll start this week with the data source. Due to the nature of the data, we wanted a noodle (relationship) instead of a join with Tableau’s new data model in 2020.2. So I set that relationship, tying together month, segment, and category:

For the line chart, I started with a dual axis of SUM(Goal) and SUM(Sales). Then, in order to get sales to only show through July, I created a parameter set to July 1, then created an ACTUAL SALES calc:

This gives me only the sales that occurred in July 2019 and before. I’ll come back to the Goal metric in a bit.

For the July 2019 dashed line, I added a Reference Line:

And for the gray shading after the reference line, I added a Reference Band:

I just matched the color of the lines by picking screen color from Ann’s viz. The tooltip here was pretty straight forward as well:

Next, I worked on the table. This was a little tricky to figure out how to get “NO GOAL” when expanding to the SubCategory. First, I created the hierarchy of SEGMENT, CATEGORY, and SUB-CATEGORY. (You may have noticed many of my fields are in CAPS this week…this table is the reason. The easiest way to get the headers to be in all caps is to rename to field to all caps. So there you go.) The hierarchy made it easy to expand and collapse. It took me a bit to figure out how to get the goal to go show “NO GOAL” rather than the goal for the category. And after trying several ideas (none of which worked, obviously), in the name of training for my live group, this was my first peek at Ann’s workbook:

The Min and Max only match when the hierarchy is expanded all the way to SUB-CATEGORY, so when that happens, we want the goal to be 0, otherwise we want the SUM(Goal). In order to get “NO GOAL”, I set the 0 value in custom number formatting:

The next thing I noticed when expanding to Sub-Category was the future months with no sales got filtered out. This was another one I struggled with, so here comes peek #2:

I forget quite often about the SIZE() function. It gives you the number of rows in the context of the view. So in this case, if the size is greater than the product of the items in Max Size (the number of rows we should have actual sales for segment and category), then we want to compare the month to the “current date” in the Parameter. Toss that on Filters, and select True, all future dates go away.

Last, I moved on to the Indicator. I played with a few ideas here, and finally landed on MONTH in Columns, and MIN(1) in Rows, with the Rows axis set at o to 1. For the colors, I needed to calculate the % difference:

Then compared that difference to the parameters setup for the red and green colors:

Once I got everything in the dashboard, I needed an action to filter the line and indicator charts.

 

And that’s it! This was a great solution to a fairly common scenario many of us run into.

Click to view on Tableau Public