Month: May 2020

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

Sean came back  for one more week with a great challenge to try out the new set actions available in version 2020.2 this week. It’s funny because those actions were like the easiest part of building this week, which is a testament to the functionality itself, I think. Anyway, here we go!

I started off with the map. Double-click Latitude/Longitude, add State to the Detail. Right away I noticed the background map needed to go away, so I opened up the Map Layers and washed out to 100%:

I then created the State Set, right-clicking on State>>Create>>Set… Then came the first set action:

So by clicking on the Map, I will add that state value to the State Set. This is one of the new things. Before 2020.2, you could only assign values to the set. You couldn’t append or remove.

Once I tested that out, I realized that Sean’s was auto-deselecting. So I added TRUE and FALSE to Detail and set my filter action:

As discussed in prior weeks, this tries to filter where TRUE = FALSE (which it never will) so it automatically unselects everything, including what you just clicked on. Best tip ever from Yuri Fal!

I’ve started trying to address the tooltips while I’m working on the sheet, instead of forgetting about them until I’ve published to Tableau Public, so here’s what this one looked like for the map:

After setting colors for the set values, I moved on to the KPIs. I’ll discuss sales, and then I basically duplicated the sheet and calcs and replaced sales with order and customers.

First, a table calculation Percent of Total on SUM(Sales) with State Set on Color. Then I worked on the title. But as it is, if you just take that table calc and add it in the title, it will give you the range of that value. I don’t want that, just the value of the states in the set. So I Ctrl+click+dragged the % of Sales from Columns into the fields pane (shortcut so I didn’t have to start from scratch), and adjusted the numerator to only give me sales when it’s in the set:

I needed the same kind of thing for the total sales, so I just did a Fixed LOD – {SUM(Sales)}. So then my title looked like this:

For the tooltip, in addition to those two values, I also needed the total sales for the states in the set:

Initially I wasn’t using TOTAL in this, but then I realized my values were only showing up in the tooltip when I hovered on the purple, not the gray. Adding TOTAL fixed that.

I fixed the axis range at 0 to 1, removed the axis label, and set the tick marks to every 20%. Then I duplicated that sheet and swapped out Orders and Customers calculations, but they’re all pretty much the same.

The last piece was the state list. I just added State to Rows and Label, and figured Sean had added ‘x’ to Rows as well. But then when I looked at his, I realized the whole state name was highlighting with the x, not separately like mine was. So I created a little concatenation:

Added that to Text, added State to Detail, and set the color of the pane to purple. I filtered that sheet on the IN/OUT of the State Set. Then I needed to add the Remove from Set action:

Same thing as the action on the map, just removing instead of adding. Truly, very simple to setup.

From there it was just setting up the dashboard, getting the proper padding and background colors (like in the state list with the gray background). And that’s it!

Click here to view in Tableau Public

After Luke‘s challenge stumped me last week I’ve been looking forward to a little redemption this week. As always, Ann‘s challenge was pretty straight forward with plenty of little twists. Because of those twists, and what it meant for my solution, I’m probably going to bounce around all over the place in my walkthrough this week. Apologies in advance…

I started off building the weekly view. Pretty basic, SUM(Sales) on rows, Week(Order Date) on columns. I would later come to find this was wrong, but roll with me here. I added the average line, and even updated the tooltips early on instead of waiting until the end! One thing I realized was when I format the date on the axis (mmm d, yy), it makes that the format in the tooltip. So I created a custom date for the week of Order Date, which allowed me to format mm/dd/yyyy for the tooltip.

Since Ann said 2 sheets, I figured I would have one for the weekly, then one for the daily. So I built the daily sheet, similar to the weekly. As we (I do this live with a group at work) talked through how to set the dates, I started down the path with Sets. But as I started on that, I realized I didn’t have a great way to get the MIN or MAX date from the set values. It was just giving me TRUE/FALSE in calculations, and I wanted the MIN/MAX values that were IN the set. As I talk through it now, having looked at Ann’s solution, I realize I could have made it work. But, we didn’t see the path forward at the time, so we did it differently! (Hooray, always more than one way to accomplish things in Tableau!)

So we started down the path of parameters instead. I created a Min Date and Max Date:

Just basic date parameters here, open to any value.

I will jump back now to point out that when I got the soon to be mentioned Parameter Actions working, switching between two sheets in a horizontal layout container, I noticed that the animations were not matching how Ann’s viz was transitioning. As we moved further into how to create a button (thinking, oh, there are buttons in dashboards now! nope…), we began to realize that the button was the 2nd sheet, and the weekly/daily shift was within one sheet. So, I already had a Sheet Selector parameter I created in order to switch sheets, so I just repurposed that:

Then I created a date field to truncate the date on day or week based on the parameter value:

I added that to columns, at the DAY level, and created a duplicate field so I could format the tooltip date value different. Then I replaced the aforementioned custom date with this duplicated date.

I also realized I needed to call out Day vs Week in the tooltip, so created a little case calc for that:

Now, back to the Min Date/Max Date parameters. I needed to get these to dynamically set based on the selected dates, so I created two parameter actions, one for each, which populated the parameter with the Minimum or Maximum Selected Date:

I also needed to change the Sheet Selector value from Weekly to Daily. So I created a Daily calculation with ‘Daily’ and added it to the Detail in the Marks shelf. That gave me what I needed to update the parameter value:

I had created a calculated field called Sheet Selector Filter that contained the Sheet Selector parameter, but when I didn’t need to use it as a filter anymore I just dropped it onto Color so I could set the Weekly gray and Daily purple.

Now that I had it switching from Weekly to Daily, I needed to create the button to switch back to weekly. Based on previous challenges, I went with a Gantt Bar, with MIN(1) on columns, and MIN(-1) on Size, which gave me a bar from 0 to 1. I fixed the axis on 0 to 1, and adjusted the height of the bar by dragging the top of the axis down. I’m realizing now I could have also adjusted the Size slider, so that’s another option. To get the label, I just double clicked on the Marks shelf and added ‘CLEAR SELECTION’ then dragged to label. On the label I center aligned, white Tableau Bold 12 font, 73% opacity. Why 73%? I have no idea, it just looked about right. Totally eyeballed it.

Now in order to make the Sheet Selector value change back to weekly, I needed a Weekly calc just like my Daily one (literally just ‘Weekly’ in the calc). Place that on detail. Then I can create that action:

Now I needed to update my Min/Max date to be the overall Min/Max of the 52 weeks (which Ann said we could hard code, so I did…). LODs to the rescue! Apparently I was near the end, as I didn’t create calcs, just edited in shelf. So I did {MIN(Order Date)} and {MAX(Order Date)}, and I needed to add my Order Date filter to the Context. Once I had those in the detail shelf, I could use them to “reset” the parameters:

As I tested the actions, I noticed that I had to click on the Clear Selection button twice in order for it to clear. So I added TRUE and FALSE to the Detail, and added the filter action to automatically deselect:

I also needed the button to only appear when the Sheet Selector parameter was on Daily. So I dropped my Sheet Selector Filter onto Filters and selected Daily so it appeared and disappeared based on that parameter. I added it to the dashboard as a floating object in the top right of the view.

Similar to needing to click the button twice, I noticed that the weekly dates I was selecting were highlighted still in the daily view:

I tried the TRUE/FALSE deselect on there as well, but couldn’t get it to go away. :o( This is the one visual aspect where I did not match Ann, which probably has to do with my using parameters instead of sets for the date selection.

For the header, I needed the dynamic purple text:

Then I created the title of the worksheet:

You’ll notice a couple WINDOW calcs there. I just added them directly to the Detail.

And we’re done!

Click here to view in Tableau Public