Category: Workout Wednesday

This week’s challenge from Luke was pretty simple, at least from a requirements standpoint. “All” we had to do was replicate the step-ranked chart with different labels on each end of a line with only one calculation on labels. Luke also included an “advanced” option to not use the square or shapes mark type.

Since I do the challenge live with a group at work, I started with a basic version. First, I created a Rank calculation on total sales:

After adding MONTH(Order Date) to columns, I added Rank to rows as a dual axis (and reversed the axis). Just to make sure the rank was calculating properly, I checked the table calculation:

In Andy Kriebel’s plain sentence version: For each Month of Order Date, calculate the rank of sales by region. For this basic version, I stuck with the Square mark for the boxes. I added Region to color, set the colors based on Luke’s viz using Pick Screen Color. Then adjusted the opacity of the squares to 45%.

For my label, I used FIRST() and LAST() to create a different label for each end of the line. In the basic version, I did a little hardcoding based on region:

Format the labels and the months, and all finished (with the basic version):

After finishing this round, we still had plenty of time, so we attacked the advanced version.

After duplicating the sheet, I looked at the options on the Squares marks card, and decided to try a Gantt chart. It showed promise, but I needed something to give it a size. So I added min(1) to size, but that made the boxes all run together. After playing around with the value, I settled on min(.9).  Then I noticed that put the lines at the bottom of the box:

I realized the Gantt bars were starting on the rank value, so I subtracted .45 so the rank integer would be in the middle:

That took care of the height of the boxes, so I tried adjusting the size on the marks card, and it widened the boxes without adjusting the height. Perfect!

I decided to also level up my label, rather than hard coding based on the region. First I created a label value to turn the rank value to 1st, 2nd, etc.

Then I dropped that calc into a copy of my prior label calc:

Initially when I added this calc to the label, things weren’t appearing correctly, so I had to play with the table calculation a bit. Here’s what those needed to be (Label (copy) and Rank as a Nested calculation):

One final hiccup I ran into was that the South label wasn’t showing up. Desireé offered a simple solution:

And with that, we’re done with the advanced chart. The spacing actually works quite a bit better with this one than with the squares, I thought.

Here’s the link to the interactive viz.

 

Sean Miller’s first challenge this week as a guest host was a great one! I actually finished this one this morning and immediately started talking to people about having this as an option in some of our dashboards. Here we go!

I started with the KPI BANs to kind of get warmed up. I just dropped the three measures into the view on top of each other, moved Measure Names to Columns and copied it to Label, and edited the text for size and color (which, thankfully, Sean gave us the hex code for).

Next I moved on to the area chart. I gathered from the weight of the line that it was a dual area and line chart. So I setup the dual axis chart, made one a line, one an area, reduced the opacity of the area to match Sean’s, and called it good. So far, pretty straight forward stuff.

Now on to the meat of the challenge…all the parameters needed. I started with the selector. I set up a string parameter with the four options Sean created:

I passed it through in a calculated field to use later. Then I created the parameter to be used for the Last N Days. This one was an integer:

The last two parameters needed were Start Date and End Date for the custom dates option. Here’s what the End Date looked like:

With all my parameters set up, time to make some calculations. The first one I started with the easy ones, Last 14/30 Days. I decided to try a Datediff to find the first day of the dates to show, and it worked! So then I added the Last N days, but instead of hard coding the number of days to subtract from the Max Date, I put the parameter in instead. Here’s what that final calc looked like:

Note: the Max(OrderDate) is in {} to make sure that it was the Max(OrderDate of the whole dataset (Fixed LOD), not just the individual row in question.

Now I could use this calculation to filter the view by saying Order Date > Date Chooser for these three options. The custom dates would be a little different, since it had a start and end date, so I made this calc for my filter:

Dropped this boolean onto the filter shelf in the area chart and started playing with the parameters in different combinations, and everything worked great! So I applied it to the BANs sheet as well. On to the selector sheet.

I took inspiration from Ann’s hack in the Superstore data in Week 2, and found a field I wasn’t using that had four values (it happened to be Region). So I whipped up this “renaming” calc:

Note: I tried just creating Aliases in the Region field, but it didn’t really work when I tried to do some conditional stuff. So I went with the calc instead.

So I added this Date Choice to Columns and Label, but I needed something to determine whether to use a filled or empty circle for the shape. Enter the Shape calc:

Pretty easy, if it’s what’s selected in the parameter, it will be True (filled circle), otherwise it’s False (empty circle). But my labels wouldn’t really align. That’s when I remembered I had already been through this in Week 2’s challenge, so I looked at my blog post  on it, and remembered Luke’s Min(0.0) trick. Played with the axis and found that -.2 to 1 worked great for spacing of the text and circles.

Now time to throw everything in the dashboard and setup the required Parameter Action.

A little testing, and everything works great! (I cleaned up tooltips and formatting as I went through each sheet) But wait…Sean’s version doesn’t show the N days box or the Start Date/End Date. I racked my brain for a bit trying to think through how to put something in a container and make it go away based on something being selected. I do Workout Wednesday live in a demo session at work, and had five minutes left before the hour was up, so I cheated and downloaded Sean’s workbook to give some closure on how to do it. Shoutout to Sean, great idea. Basically, he created a couple sheets (one for N days, one for custom dates) that had “” on Text and Columns, and a filter to only show the sheet when the respective option was NOT selected. Then you put that in a horizontal container with the parameter entry box to the right of it in the container. Then when the respective parameter option is selected, the sheet “disappears,” moving the parameter box to the left. Float a blank box (colored the gray of the background) where the parameter box sits when the sheet is there, and you have a “hiding” parameter.

And just like that, we’re done! Thanks for following along.

Tableau Public link here

 

Lorna’s challenge this week felt like a good mix of familiar and unfamiliar for me. I continue to be amazed (though no longer surprised) how each week I can complete the same challenge in a different way. This week was no different, and I even downloaded Rosario Gauna‘s viz for this week and it’s even different still. A testament to the flexibility of Tableau.

So I started off getting the grid/small multiple setup figured out before I cluttered everything up with dots. My usual go-to is to create a columns and rows calc based on the number of items, but they are table calcs. (I pulled them from a Workout Wednesday by Andy Kriebel a couple years ago, and just saved the file and open it up whenever I need them. If memory serves, it’s a variation on calcs developed by Chris Love and/or Jeff Shaffer.) Lorna’s challenge SPECIFICALLY stated, “No hard coding of months or table calcs.”  BOO!

Ok, no biggie, a quick search gave me a quick intro to the Modulo function. There’s a great post on the forums about it. Basically, it divides a number by another and gives you the remainder. In this case, I need to make 3 columns, so I’ll divide by three.

When I initially created the calc things weren’t lining up with what Lorna’s looked like. Then I realized the months were out of order because 3/3 has a remainder of 0, so March was in the first column, followed by January and February. By subtracting one before the Modulo function, it solved that problem.

Once I had my grid in place, I added the day number, [DAY(Order Date)], to columns. Now I needed the hours of the order. Per Lorna’s instructions, I joined Order Times to the Superstore data, so I had the Time of Order field. I found that Lorna’s view was just using the hours, so I just built a calc to give me the left two characters:

This is one where there were lots of ways to do it. Rosario did a Dateparse of the 00:00:00 string, then a Datepart to get the hour:

Lorna used MakeDateTime with [Order Date] and [Time of Order], then created a Custom Date to get the Datepart of hours:

All three of these gets you the same thing, two-digit hours. I pulled hours into Rows, and reversed the axis as Lorna did. With both axes and the small multiples all setup, it was time to add the order count, [COUNTD(Order ID)], to the view. I added it to size and color, and played around with both until they matched up with Lorna’s.

To get the range of hours orders were placed, I decided to try a Gantt  chart on dual axis. So I started with an LOD to get the minimum/maximum hour for each day:

One more calc to get the length of the Gantt bar:

From there, I brought the Min Order Hour to a dual axis with Hours, added Hours Length to size, synchronized the axis, and played with the size to get the bar to match up with the dots. The last thing on Lorna’s sheet was a trend line, added on the hour axis.

After finishing the Days sheet, I duplicated the sheet to make adjustments for the weekday view. I replaced DAY(Order Date) with WEEKDAY(Order Date) and needed to adjust my calcs for the Gantt bar. I added a Month Datetrunc and changed the Day Datetrunc to Weekday, but it wasn’t working right. I played around with it for about 10 minutes before I finally figured out that I needed the Weekday Datepart, rather than truncating based on the day. The difference was like looking at a date level based on a weekday, rather than Monday, Tuesday, etc.  Here’s what that final calc looked like (just doing the Min Order Hour here, the other two are the same Fixed portion as this, with the aggregation the same as the prior calcs):

After replacing the Gantt calcs with these new ones, it’s time to setup the sheet switcher. I did this in last week’s challenge (and actually a good chunk of this afternoon at work), just a string parameter with two options:

Then you just create a calc to pass through the parameter and use that as a filter on each sheet. Add the sheets to a vertical container on your dashboard, hide the titles, and as you change the parameter, the other sheet collapses so only one sheet is visible.

The last (almost) curveball was that the gif on the challenge page looked like it might have been another sheet with shapes:

Once I opened up the workbook on Tableau Public, I realized it was just the parameter formatted to a larger font, and the radio buttons increase in size accordingly.

With that, challenge complete!

Click here to view it on Tableau Public.

I’m not sure if it’s that I was trying to move quickly (since we started doing #WorkoutWednesday live at work with a few colleagues so they can see some of the methods to make things work in Tableau) or what, but Ann’s challenge this week had me flustered for a bit. By the end, I realized I could’ve done it like Ann in only two sheets, but the path I started down (and was too lazy to get off of and start over) used a few more sheets. Thankfully, “# of sheets, up to you” was the second item in the requirements!

Ok, so I started with building two parameters. One for the Metric, and one for the date range. These were just basic string parameters with 3-item lists.

Once I had the parameters set up, I started working on the dates. Oddly, Ann’s data went through 2019, but mine only went through 2018. So I made a quick calc to add a year to the Order Date:

Then I created a calc to dynamically set the date level based on the parameter:

I also created a calc that filters the date to the desired duration:

When I first added Date Level to the view, I set it as a Continuous Date. So then I realized I would have trouble adjusting the formatting of the axis, so I decided to make a sheet for each parameter option (Daily, Weekly, Monthly) and just switch which sheet was shown on the dashboard by the parameter. I needed one more calc that just brought the value of the parameter in to filter the sheets.

(*Sidenote: I realized as I went forward that I didn’t want the date to be continuous, it needed to be discrete. This is the thing where I was so far down the path, I didn’t want to bother changing everything. With a discrete date dimension, I could’ve just made them strings, formatted in the manner we needed them to be in. That’s the way Ann did it.)

For the metrics, I used a similar case/when calc:

[Items per Order] was just SUM([Quantity])/COUNTD([Order ID]). This case statement adjusts the measure based upon which parameter option is selected. I added another calc that passes through the parameter value in order to set the colors. I formatted the date axis in each of my sheets as follows: Monthly – mmm ‘yy, Weekly – \Week ww, Daily – mm/dd. The week was a hard one because when I typed the W for Week it put the week number in it. So I picked one of the formats that showed weeks, and saw that there was a backslash in front of the w that showed as text. Plugged it in, and it worked!

I skipped the labels at this point and just pulled my Daily/Weekly/Monthly sheets into a dashboard to feel like I was making progress. The key to sheet switching is to bring all three (or however many) sheets into a horizontal container, hide the titles, and set the sheet filter for each. For example, with the Date Switcher set to Last 12 Months, I added the Date Switcher Filter (which is just the parameter value), checked Last 12 Months, and that’s it. Then you change the parameter to weeks, set the filter on that sheet to Last 13 weeks, and so on for the Last 14 days. Now each sheet only appears when that respective parameter has been selected. And when there is no data in a sheet within a horizontal (or vertical) container, it minimizes to a couple pixels wide.

Now for the legend. I tried quite a few things trying to figure out how to get the shapes so neatly aligned with the text. I knew I needed to use shapes, with an open circle when the item wasn’t selected and a filled circle when it was. I tried to figure out how to get them all in one sheet, and eventually just bailed for the three sheets version, one for each metric. So I created three boolean calcs, one for each metric, that just had [Metric] = ‘Sales’ (or Profit Ratio or Items per Order…for this description, I’ll use Sales; the other two used the same process). I dragged that onto shape, and had to switch the parameter to set both True and False values. Then I double-clicked in the Marks card and added ‘Sales’ for the label and also the color.  I aligned the label to the right, and the alignment was still kind of funky. I tried adding MIN(0) to the columns, and that made the text align really well with the circle. However, when I put them all together on the dashboard, there was too much extra space to the left of each one. Then I remembered something I watched Luke do in his solution video for week 1. If you make it MIN(0.0) instead of just MIN(0), you can set the axis at a decimal level. So I played with the axis a bit, and setting the low end to -0.1 and the high end at 0.5 worked like a charm.

With the legends in place, it was time to add the parameter actions. Amazingly, this was almost the easiest part (especially after struggling through the legend stuff for about 30 minutes). Here’s what that looked like:

This worked because what I typed in the marks card matched exactly what the parameter values were, so I could use that as the Field in this case.

Another thing I saw Luke do in that video was automatically deselect when an item is clicked on for a parameter action. Compared to the workarounds I’ve done in the past (Joshua Milligan’s technique is the one I’ve used the most), this one is super simple. (I believe it can be attributed to this tweet from Yuri Fal)  Create a calc named TRUE, with calc TRUE. Then create a calc named FALSE, with FALSE. Add both of these fields to the detail (in this case, I added them to the three legend sheets). Then create a Dashboard Filter action like this:

And you magically have items that deselect the moment after you select them, so you get the magic of the parameter action without the lack of magic of having to click twice on the next one you want to change (or having highlighting that you don’t want).

Now the only thing left was the labels. I cringed a little when Ann’s requirements said to use REGEX for the number formatting. While I’m aware of what REGEX is, I have very little experience with it. But I decided that this is why I do Workout Wednesday, so I started searching. I found a great regex cheat sheet posted by Ken Black here. This didn’t really help me solve the problem, but it helped me make a little more sense of the solution once I found it, and thought it was a great resource to share. Then I found this gem in the forums that was doing almost exactly what I needed to do:

Here’s where I ended up on my label calc:

I don’t have much explanation of the REGEX…total copy/paste aside from rounding the value. I had to round the profit ratio twice in order to get the three decimal places of the original value for the two digits + one decimal place of the final desired value.

Quick touchups on the tooltips and checking all the formatting, and we made it to a solution!

To view in Tableau Public, click here. Thanks for following along!

This week’s #WorkoutWednesday2019 challenge was by Curtis Harris. We needed to build a heatmap of cohort retention, with a marginal histogram and parameter-based selector. Here’s the goal:

I decided to just work my way from left to right. I first started with a Datetrunc calc to get the cohorts into weekly bins starting on Monday.

But then when I compared this date to the Order Week values, I noticed they were already in Monday weeks…so I moved on to identifying the first week each customer made an order. I used an LOD to find the earliest week for each Customer ID:

Then, in order to find the number of customers per cohort, I checked to see if the Cohort week matched Order week and populated Customer ID:

Then I just pulled in my cohort week and a COUNTD of [New Customers] and I got customers by cohort. I stopped there for now and moved on to the heatmap. The heatmap needed to show a retention rate for each week and cohort, so I started with a DATEDIFF for the weeks:

This gives me the week numbers for the columns across the top. Then I built the retention calc:

Again, I used an LOD here. When I reviewed Curtis’ workbook after I finished, he uses a Lookup function. I’ve struggled whenever I use a Lookup to get it to do what I want, and I find I can more accurately define what I want with my LOD calcs (not sure how I did anything before LODs came along).

Drop Cohort Week to Columns, DAY(Cohort ) to rows, sort desc, and I get the start of things.

Now I needed to create the parameter for selecting 10-26 weeks. I noticed that Curtis’ parameter wasn’t just a number, it showed “26-week.” I didn’t want to type that 17 times, so I decided to try something new. I opened Excel, put 10-26 in one column, then concatenated the number with “-week.” Then I copied that and clicked Paste from Clipboard in the parameter list area:

This brings all the values in, and allowed me to keep it as an integer instead of a string value:

Next we need to use this to filter what shows in the view.  I decided upon two filters, one to display the right Cohort Weeks (columns) and another to show the Cohort date (rows). First, to show only the Cohorts with completely mature data:

Back to my trusty LOD, I found the Max Cohort Week by Cohort and return True for any that are larger than the parameter value. That got me here:

Now I need to have it only show columns 1-26. That’s easy enough:

I picked a Yellow/Green Sequential color scheme, not sure where Curtis found his Vidris palette, but I felt like this did the job, and the requirements said any sequential scheme was fine. Uncheck Show Header on the DAY(Cohort) and we just have the tooltips left. This part was pretty straightforward, until I was watching a bit of Andy Kriebel and Lorna Eden‘s head-to-head WW Live webinar. Andy had the eagle eye and noticed that the Actual Week date had leading zeros on the day.

This requires some custom date formatting. So I right clicked on that date on the marks card and adjusted (make sure you’re in the Pane, not the Header in the format pane):

The dd here adds the leading zero if the day is only one digit. (mmmm is the full month name)

And with that, on to the marginal histogram. I kept my Show in Heatmap filter to keep the Cohort rows, and created another calc to filter just to the selected parameter value:

Then I pulled DAY(Cohort) into Rows, Customer Retention into Columns, and added a MIN(.5) to the columns shelf. Make it a Dual Axis, Synchronize the Axis, and now I have my requisite 50% bars with the retention for each cohort shaded.  In order to match the grays I just did a Pick Screen Color in Edit Colors. I added a Constant Line at .25 to match Curtis’, but then needed to add “Week 26” to the top of the chart. So I created a quick calc and dragged it onto columns (then hid field labels for columns and aligned left).

I think I started the histogram from duplicating the heatmap, so my tooltips were already in place, just had to make sure they were complete (hovering on the light gray didn’t have the retention rate for some reason).

On to the BAN…which proved to be more difficult than I expected, but more on that in a minute. I kept the Bar Graph Week filter, moved Customer Retention to Text, and voila!  21.2% for week 26….except Curtis has 14.2%. No matter how I tried, I couldn’t get 14.2%, so I reached out to Curtis. He explained that I need to remove the cohort from the view so it was sum(current week customers)/sum(all week zero customers).  While that made the math work and I got 14.2%, it didn’t really make sense to me why we would count cohorts in the denominator that didn’t have the chance to mature to the week we’re viewing. Andy mentioned the same thing on their webinar, so I at least didn’t feel like I was barking up the wrong tree.  So I followed up with Curtis, and he confirmed that he just forgot to add a filter. 21.2% should be the Week 26 value.

I bring this up not to point out that Curtis was wrong and I was right, but rather that it’s very easy to forget to apply filters and sometimes we need to step back and think about what the numbers are saying. (Aside coming…feel free to skip to the next paragraph for the final dashboard assembly) As an example, a few months ago I was putting together a dashboard at work looking at how often people saved items to Ancestry from one of our sister sites. I shared it to the stakeholders, and got the following response (give or take, with numbers changed to protect the guilty): “Wow! I realize it’s in the Sandbox,” (thankfully, I’ve trained my stakeholders to understand that if something’s in the Sandbox, I’m still working through the numbers, and they should question any and all conclusions)” but a median of 45 items saved per customer, that means that 50K users are saving 45 things to Ancestry! If these numbers hold true, that’s incredible!” Yeah, that gave me pause.  So I dug in, and realized I was missing one filter, and forgot to add another to the context in order to properly render an LOD. 45 changed to more like 7, which was still good, but definitely not 45. So, don’t forget to double-check your filters.

Ok, it’s late now, so I’m going to shortcut.  Here’s how I arranged things on the dashboard:

If you’re not familiar with using containers for everything, check out Curtis’ post on that very thing. (Although I shortcut and don’t add the floating Vertical, I just drag the vertical on and then remove the Tiled container from the item hierarchy)

And that’s it!  Hopefully this was helpful. There were definitely several differences between how Curtis did his and how I did mine, so take a look at both of them!  (Curtis used one worksheet for the customer count and heatmap, while I did separate ones; he used Lookup rather than LOD; I’m sure there are more)  Here’s mine if you want to check it out. And thanks for following along!