YetterDataViz

I was excited to start Luke‘s challenge this week because at first glance I thought I had the solution pretty much in mind. That thought always betrays me. Always.

Thankfully he provided the calculations needed for the rows/columns of the cartogram. That was an easy start. Then I added Report Date to Columns, and People Positive Cases Count on Rows. With Province State Name on Detail, the cartogram started to come together. But per the challenge, we need new cases from that field, so I added a table calc of Difference From, and selected from Previous. I knew I would end up nesting that calculation for a 7 day average, so I ctrl+click and dragged it to the Tables pane on the left, which gave me the calculation:

To get the 7 day average, I used a WINDOW_AVG of New Cases:

After adding this to rows in place of New Cases, I set the chart type to Area, and added the 7 day average to Label, and set it to show on Maximum Value. Then I noticed that the peaks weren’t matching up with what Luke had. So I had to normalize it. In order to normalize, I need to divide the 7 day average by the maximum new cases value for each state:

Things were looking pretty good at this point. But then when I did my usual labelling mechanism for these kinds of charts (multiplying the value * 1.4 and putting it on a dual axis), I was unable to get the line underneath the trend to work. I played around with that and a bunch of other attempts, until I finally just took a peak at Luke’s workbook. Even with that it took me a solid 10 minutes to figure out what he did. I had identified from the tooltip on the state label there was a separate date, and I needed to put that on a dual axis with Report Date. Based on it being a center date (the state label is centered), I created a calculation to make it the center of the date set:

This one finds the difference (in days) between the earliest and latest report date in the data set, divides that by two, and adds that to the earliest date in the data set. So when I add that date as a dual axis with the report date, and synchronize the axis, it puts a point right in the middle of the axis. So when I added a label, it put the label at the bottom of the chart. No matter how I aligned it. I finally looked at the calculation Luke had on rows, and it was a different way of doing my 1.4 value:

This one took me a minute of talking through it to figure it out. But then I realized, for the Center Date axis, there will be more than one Report Date, so it puts those points at 1.4. For the values on the Report Date axis, there’s only one, so it does the normalized value. Once I saved that calc, the state label popped right up to the top. With that in place, I needed to update the tooltips:

Setup the title and caption, and drop the worksheet into the dashboard, and we’re done!

Click to view in Tableau Public

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

This week’s challenge collaboration by Ann and Jami was all about table calcs. I really like doing table calc challenges (even though I struggle through them) because it really helps me improve my mastery of them. This week had a ton of little things that I didn’t really see working through it for the first while. It’s funny, I sat down Thursday night to work on this blog post, but I had “a couple things” I needed to finish, namely the days streak calculation. A couple hours later I had found like 5 different little formatting things I hadn’t even noticed on Wednesday. But I really enjoyed this one, and it was very interesting to see how things look in my area.

I started out like Ann suggested, with the table. (At first, I manually set the state to Tennessee so I could compare with the original. After I got further along, I took that off and replaced it with the dynamic filter I needed.) I started by making a calc for New Cases that would give me the last date’s value:

Yes, I realize I just went on for a paragraph about how this challenge had table calcs, and I went straight to an LOD solution instead. Looking at things now from the end, I did not need to do this. But I did it anyway.

Then I grabbed the MAX(People Positive Cases Count), and dragged it into the view to give me Measure Values on Text.

Next we need the 3-day and 14 day moving average calcs:

These calcs are telling Tableau, “I want the average sum of new cases for two (or 13) rows before my current row to my current row. In order to do this based on date, we have to add Report Date to Detail. But that gives us a bunch of data for every report date for each county:

In order to get just the last date, I made a calc of LAST() = 0 and added it to the filter shelf where it equals True.

All three of these table calcs…the two averages and the LAST need to be computed using the Report Date:

That gives the most recent data for all of the Measure Values. This is why I didn’t need to use the LOD I started with, because having the LAST filter would’ve done the same thing for me.

One of the little things I noticed later on was that the table was showing the selected county at the top, followed by the rest of the counties sorted by reported cases descending. Also, the text for the selected county was darker than the rest of the counties. I’ll jump into the parameter I created to select the State/County.

At first I thought I could just create a combined set, and it would’ve been so smooth. But for some reason, it wouldn’t let me combine the State and County sets I created. I could combine those with other test sets, but not with each other. It was weird. So instead, I created a calc that combined the two:

Then I created a parameter, and added the values from this calculation:

Then I created my state and county filters:

To get the selected county to the top, I added County Highlighter to Rows, sorted to get True to the top, and unchecked Show Header. Then I added County Highlighter to color, and set the dark and light gray. Here’s the end result of the table sheet (I’ll get to the tooltips later):

 

For the map, we just need to add state and county to the detail, add a copy of the County Highlighter (since we’re using different colors than the text table, we need a separate dimension), and we need the increase/decrease calculation. We need to compare the 14 day avg to the 3 day avg:

In hindsight, I could’ve just used my 14 day and 3 day calcs I already had, but this helped cement how to build window calcs manually (vs doing so from the pill menu). Then we add this to color as well (you can add it to Detail, then click on the Detail icon and change it to Color). Then all the false values get set to white, and you need to select a county that has each of the increase/decrease/no change values to set those colors properly.

We also need to have Report Date in this view and LAST()=0 in the filters, in order to calculate the Increase/Decrease properly.

For the bar chart, I started with SUM(People Positive New Cases Count) on Rows and DAY(Report Date) on Columns. Set it to bar, with white color and a black line border. Then the line needs to be dynamic based on the selection of 14 day or 3 day. So I created another parameter:

And then my case statement to use the chosen calculation:

Add that to Rows on the dual axis, and synchronize the axis, along with adding Increase/Decrease to color. Because this needs to be on the county level, I added County Highlighter to filters. In order to get the reference line to show the date of the first reported case, I added People Positive Cases Count to filters, with a minimum value of 1. Then I could add a reference line and use the Minimum Report Date:

Once I had these sheets done I started putting together the dashboard layout. A little thing I noticed was the gray background for everything between the two black lines at the top and the bottom, so I noted that I needed a vertical container within a larger vertical container that had a gray background. The Search “State-County” is a Type-in Parameter Control of the State-County parameter. It was this point I noticed the legend wasn’t really a legend. I thought it might be a sheet, but then it wasn’t acting like a sheet either. By this point, I was just in it for figuring out how the heck they did all this stuff, so I took a look at Jami’s workbook…

IT’S A TEXT BOX!!! How have I never seen this before? It’s literally just a box character, with the font changed for each box to match the color. I can copy/paste it here (the color doesn’t transfer from Tableau): █ DECREASE █ INCREASE █ NO CHANGE. This was kind of mindblowing, and had nothing to do with table calcs.

I saved the hardest for last, and I knew it would be the hardest. I thought through it a bunch of different ways, did all kinds of searches, and kind of had an idea of what I might need to do, but at this point I think I was going on 3+ hours of working on this (which is way more than I usually try to spend). So once again, I was in it for the learning, so I took a look at Jami’s calculation. And it made no sense. I was literally looking at these calcs thinking, “how in the heck is that working??? That seems backwards…” I finally had to pull a worksheet together with the calcs to see what they were doing over time, rather than just the last day’s value, so I’ll walk through that here. First we need the Tally Count:

So this is saying if the Increase/Decrease value is the same as the one before, then 0, else 1. Then take the running sum of this value. And we’ll compute using Report Date. Here’s what that looks like in chart form:

So whenever the Increase/Decrease is the same, it stays at the same level. So then we can follow up with Total Days:

Similar concept here, but if the tally count value is equal to the LAST tally count value, then give us 1, and a running sum. This really is an amazing calculation combo, and putting it in chart form really helped me grasp what was going on, because the 1’s and 0’s seemed backwards to me as I was just trying to picture it in my head. With this calc complete, I could then fix all the tooltips that needed this value. Pretty much every calc in the whole workbook are computed on Report Date, which at least made that part of the table calcs easy.

And with that, three days late…we’re done!

Click here to view in Tableau Public

 

Luke has this crazy ability to build stuff that you think should be pretty easy, and then unless you know the exact trick, you’ll spend 30+ minutes getting close but not quite there. That was my experience with this week’s challenge.

I felt kind of ridiculous this morning as I was dragging different combinations of month, year, index, adjusted date calcs, and other things around for about 30-40 minutes while a couple colleagues watched on. I had a couple inklings of how I needed to get stuff to happen, but couldn’t step back far enough to think it all the way through. My closest attempt for a bit was setting all dates to the month + 1/2019:

When I added that to columns, and added INDEX(), I could get the bars the way I wanted with spacing we wanted:

The problem was I couldn’t figure out how to get the month to the bottom axis. No matter how I tried to outsmart Tableau, it didn’t want to do it.

As I was talking through what we needed to do, I touched on how it seemed like it was just mapping the bars on the timeline, but I couldn’t wrap my head around that.

After a couple tries, I found someone whose workbook I could take a look at (since Googling got me nowhere today). Katharine Rosacker was the lucky winner. She had two calculations I ended up basing mine on. One to set all of the Order Dates to the same year, truncated at the month level. Here’s what mine looked like:

This got all of the data points to be in the same year. Then the other calc adjusted each year to a different day according to the spacing requirements:

Effectively what this does is move January 2016 from 1/1 to 12/21, January 2017 to 12/26, leaves January 2018 on 1/1, and moves January 2019 to 1/6. Then the same concept for each month. Once you add this Centered Date to Columns, with YEAR(Order Date) on color, it’s magic!  Then a quick adjustment to the size to set the bars to 4 days wide (leaving 1 day in between):

I added MONTH(Order Date) [continuous] to the Detail so I could use that in the tooltip, since the Centered Date showed the month before for 2016 and 2017.

I noticed that Luke’s Sales axis didn’t show the 0 value on the tick mark. Try as a might, I couldn’t figure that one out, unless I set the axis to start at 1 instead of 0 (which he didn’t do). I’ll be curious to see how he did that.

For the legend at the top, I created a dimension:

Add that to columns, along with min(0.0), with YEAR(Order Date) on Color and Label, and you have a little legend.

Throw it all in a dashboard, and we’re finally done! My thanks again to Katharine for the calc inspiration this week. I was S-T-U-C-K.

Click here to view in Tableau Public

This week was our first challenge from Ivett Kovacs, and it involved something I’ve never actually done: polygons. I’ve seen them talked about, and understood the general concept of how they work, but it was definitely helpful that she shared her blog post about how to work with polygons. Because it was a new thing and took me a little longer than normal, there were a few things that weren’t quite a pixel perfect match for me because of my available time. However, I tried to focus on the key aspects: polygons, with the profitability line, and expansion from grouped view to subcategory view.

First, it took reading her blog post to understand what she meant by “use 3 tables as 1 source to plot the three different measures.” I needed to union the table to itself so I had three instances of each thing, in order to plot the three corners of each triangle. To do so, I created a Path Index calculation that just had INDEX(), and dropped it on Path in the marks card. Then I added Table Name and Sub-Category to the detail, and set the table calc to use Table Name:

At first I tried putting the Review values on one axis, and combining the Budget and Revenue values on the other axis, but realized that really wouldn’t work with the scale difference. Then I realized I just needed to figure out how to tell Tableau where to put the dots. So the X value was easy enough:

If it was the first value (the Review), then X should be 0, otherwise I wanted the other two at 1. The Y value was quite a bit trickier. I tried adjusting the values for all of the metrics to be out of 100, but that didn’t quite work for me. After a few minutes of trying to figure out how to get my shapes to look like Ivett’s, this is where I confess that I peeked. I didn’t actually end up doing everything like she did, which is probably why there are small differences, but I got the inspiration I needed to think through normalizing all of the metric values.

***SIDENOTE: I didn’t have to actually download Ivett’s workbook from Tableau Public in order to do this. I used the new Explore option, in Beta right now:

It’s basically like WebEdit, but for workbooks that you don’t own. It worked about the same, as well. Really handy when you just want to check a formula out real quick…***

So I started with the Review metric:

This takes the average of the Review values (by subcategory, because that’s in the detail), subtracts the smallest review score, and divides that by the difference between the Min and Max review scores. I did the same thing for the Budget and Revenue metrics. And then I created my Y value:

Similar to X, I just needed to tell Tableau where to plot each one, based on the Path Index value (1, 2, or 3).

IT DIDN’T WORK!

I went back through each calc, trying to figure out how on earth the Tables value was higher than Bookcases, why it didn’t look like Ivett’s, hoping I wouldn’t have to figure out some other crazy thing. I looked at the calc values they created, trying to figure out how a simple calculation could come up with something so wonky. Then the words that often came out of my dad’s mouth (he was a high school math teacher for 30 years) rang in my ears: Please Excuse My Dear Aunt Sally. You can make a wonderful Tableau calculation, but if you forget basic arithmetic, that calculation will lead you astray. Let’s look at that Fixed Review calc again:

Initially when I created the calc, I had left that set of parentheses off. So while I thought I was subtracting the min from the average then dividing by the range, I was actually dividing the min by the range and subtracting that from the average. (I hope you enjoyed today’s arithmetic lesson, because I sure did. Especially because it meant my calcs worked and I didn’t need to figure something else out!)

Next I needed the lines, so I just made a copy of Y and replaced the Budget value with null:

Then I needed to figure out if it was profitable or not, so I needed to calc Profit:

Because I had the values in the data 3 times, I often found myself using LODs to get that single number I needed, in this case Revenue and Budget for the Profit. Then I created another to use on Color:

I was now to the point that I needed to setup the interactive part to expand to sub-categories, so I created a Set from Sub-category. Then I created a counter to tell me if there were any values selected in the set:

Then I created the dynamic label for the chart:

The key here is the inclusion of the LOD brackets ({}) around SUM(Set Counter). I did it without them first, and after I setup the action, it just showed whichever sub-category I had chosen, and the rest still showed as Click to Expand. So I needed to calculate the counter on an overall FIXED level, and then it worked like a charm with the action.

Speaking of Set Actions, here’s what that looked like:

It was important that clearing the selection would remove all values from set, based on my counter calculation.

As I checked my functionality with Ivett’s, I noticed that the label for the sub-category went away and just had the Review value when the view expanded to sub-categories. So I used the same principle as my Label and created a copy:

Added that and AVG(Review) to Label on the Y (copy) marks card, and we were looking pretty good. I played around with the ‘Avg REVIEW’ axis label, but came to the conclusion that wouldn’t work for me since the fixed axis showed the thumbtack, and Ivett’s did not. So I added ‘Avg REVIEW’ to the Rows, rotated the label, and turned of Show Header for the Y value.

For the tooltips, I needed Margin in addition to Profit and Review metrics. For that, I needed the sub-category revenue:

And then I just divided Profit by that:

These values didn’t match Ivett’s, but I couldn’t figure out why. I was doing the math based on the values, and mine gave me what I expected a margin to look like, so I left that one alone. As I looked through Ivett’s after I was done, it seemed like she used SUM rather than AVG for one of the calcs, which would’ve been essentially double-counting one of the values (I don’t recall which).

For the legend, I duplicated the main worksheet, filtered to Chairs (since it looked most like her legend), and messed with the axis to get the spacing right. I switched the category label to be Avg Review, and used annotations to put the Revenue, Budget, and Sub-category labels on (best done after the chart is sized how you want it in the dashboard).

For the color legend, trusty ol’ MIN(0.0) to the rescue, yet again:

The dashboard layout took me a little while. I mostly used containers and padding. When I looked at Ivett’s afterward I noticed she used blanks for padding, which was my go-to until I started realizing I could just edit the padding, and that was easier to adjust than the height/width of a blank object. Here’s what that layout ended up looking like:

The floating Blank above the Tiled is the transparent blank I put over the top of the legend so it wouldn’t look like it was selectable when hovered over. And that’s it!  This was definitely challenging, but it was great to figure out something different from the normal usage.

Click to view in Tableau Public

 

 

This week’s challenge by Lorna focused on building with the automatic phone layout in mind. I’ve played with custom layouts in the past, but haven’t really worked with the automatic layout before. Lorna was kind enough to provide the hint that the layout processes in a z pattern. It just took me a few tries before I processed what I needed to do in the dashboard layout.

To start, I built the line chart. Here’s what that setup was:

In order to get the date to format as the first letter in the axis and month + year in the tooltip, I created a custom date and added that one to the tooltip:

Then I moved to the YTD sheet. I placed YEAR(Order Date) on Rows, added MIN(1) to columns, and SUM(Sales) to Text. For the YoY calc, I used a lookup function:

In order to have the lookup work properly (at least easily), I couldn’t filter to the last to years, so I hid them. This may have been a little more manual than Lorna did, but was something I chose not to spend a ton of time on as it was not the primary focus of the challenge. I added that to Text, and adjusted the number formatting to include the up/down arrows:

The next piece is the Top 10 customers by sales. Interestingly, Lorna’s shows Top 10 products by sales, but the requirements said customers, so that’s what I did. I right-clicked on Customer Name, then Create…Set:

Then I selected Top, and did Top 10 by Sum of Sales:

Then I added that to Rows, with SUM(Sales) on Columns. That gave me the In/Out of the Set, but I need the members of the Set. So a right-click on that pill, and you can pick Show Members in Set:

For the tooltip, I needed to add Customer Name in order to display that and the Top 10 label. (At least I was struggling with that, and this fixed the problem for me)

I duplicated the trend graph and YoY tables and replaced Sales with Profit. For the Top 10/Bottom 10, we needed to do something different. So I created a set for the Top 10 customers by profit similar to the sales one. Then I created a Bottom 10 set to match. In order to get them both to show up, I created a combined set:

Then I added the combined set to Rows, showing members of set, and the Top 10 set, showing In/Out, and changed the alias of In/Out to Top 10 and Bottom 10. I also added the Top/Bottom set to Filters.

Now on to the dashboard arrangement. I started with the sales charts and profit charts vertically arranged next to each other. But, per Lorna’s note, it follows a Z pattern, so that didn’t work because it ended up putting the YTD views one on top of the other. Then I tried just stacking sales and profit stacks on top of each other. That worked as long as I made the dash 2000 pixels long. However, Lorna’s requirements said 1200 x 800. When I did that, it really looked weird and messed everything up:

So I was wrapping things up, deciding I would just need to make the height 2000 px, and it hit me…Z pattern. Left to right, then down and left to right again. If I arrange the sales charts left to right, then the profit charts left to right below that, it will do them in proper order on the phone layout. So I made that switch:

And it worked!

Click here to view in Tableau Public