Workout Wednesday 2021 – Week 12

This week’s challenge from Sean was a focused one. Like many others, I often jump to Fixed LODs to solve my calculation needs. This week’s challenge forced me to NOT use Fixed.

The first thing I did was add Sub-Category to Rows. Then created a calc to include the Manufacturer:

Include adds the manufacturer into the mix, summing the quantity at the level of Manufacturer AND Sub-Category. Then we can take the AVG of those manufacturer quantities for each Sub-Category. That got me the same quantities for each Sub-Category as Sean’s view. Next I needed a way to highlight the selected Sub-Category. First I tried a Set, thinking Set Action would work to select. Later on, though, I decided a Parameter would be better. So I created a parameter:

I added values from Sub-Category (even though that probably wasn’t totally necessary, since I would be passing the value through a Parameter Action). Then I created the action:

Next I created a calc to check if the Sub-Category matches the parameter value:

Then I added that to Color and matched them to Sean’s teal and gray.

For the variance, I decided to use another parameter action to pass the average units into a parameter value that I could then use to compare the other Sub-Categories to. So first, I created the parameter:

Then the parameter action:

With the parameter value in place, I can calculate the variance:

Add it to Columns and Color, and we’ve got the bar charts setup. That left some formatting to match up, turning off gridlines, axis rulers, tick marks, and zero lines. It took me a minute to figure out how Sean got a zero line on the variance chart, but not the quantity chart. So I decided to try adding a constant line to the Variance axis:

Then I formatted it like a zero line, and it worked!

Adding the gray shading to the variance chart was some column banding, but only the pane, not the header:

Last thing after the formatting was the tooltip. I needed a calc to show more/less:

I also needed to get an absolute value of the variance so there’s not a ‘-‘ in front of the negative variances.

The final tooltip looks like this:

Drop it in the dashboard with the title, and we’re done! It was really good to think about other ways to do things without defaulting to the Fixed LOD.

Click to view on Tableau Public

Workout Wednesday 2021 – Week 6

Sometimes I wonder how Ann Jackson comes up with a solution for a really difficult problem that is pretty simple. This week’s challenge was one of those times. It made sense eventually, but it took me a bit to get there. The challenge was to create a text table, but only using Measure Names and Measure Values. At first I thought that shouldn’t be too hard, until I processed that Measure Values meant everything had to be a measure, EVEN TEXT AND DATES!

I started with the easy stuff, current and prior year sales:

Rather than hard coding 2020 in as the year, I decided to find the YEAR of the Max(Order Date) of the whole dataset (hence the Fixed LOD {}), and if that was the same as the YEAR of Order Date, return Sales. In the case of PY, just the Max Year – 1.

I dragged MONTH(Order Date) to Rows (the discrete month), and SUM(CY SALES) to Text. Then I dragged SUM(PY SALES) on top of the CY SALES figures:

This is usually my default way of getting Measure Names and Measure Values into the view quickly and easily.

From here on out, I can just add metrics to the Measure Values shelf. Next I calculated the Delta (Δ):

Then I knew I needed some additional formatting to include the + and -, so I select Currency (Custom) and set the negative values to have the minus rather than parentheses, and then switched it to Custom and added the + on the positive values:

Next I went to % DIFF, which was also a relatively simple calc:

I took a stab at the dates, but realized that wasn’t going to work to put in Measure Values, so I moved on to RANK. At first I thought I could just do an IIF and return 1 or 0 and change the aliases…except you can’t set aliases for measures. I had struggled through the checkbox and X, the dates, and now this, so decided it was time for some insight from Ann’s workbook. I took a look at her RANK function, and as soon as I saw 1 and -1, I realized what I needed and what a genius solution she had. So then my RANK calc looked like this:

Then I went to Number Format and set positive values to “TOP” and negative values to “BOTTOM”:

VOILA! It worked!  Then I knew what I needed to do for CY vs LY as well:

Then format similar to the top/bottom but with the text characters:

The dates still had me, even with Ann’s hint of how dates are numbers too. So after reviewing her date calcs, this is where I landed:

In plain(ish) English:

{ FIXED DATETRUNC(‘day’,[Order Date]):SUM([CY SALES])}

-Calculate the SUM(CY SALES) at the day level (I realized later I could’ve just used Order Date, but I’m used to most dates being date/time, so I default to a DATETRUNC)

{ FIXED DATETRUNC(‘month’,[Order Date]): MAX({ FIXED DATETRUNC(‘day’,[Order Date]):SUM([CY SALES])})}

-Find the MAX of that daily sales value, at the month level

= { FIXED DATETRUNC(‘day’,[Order Date]):SUM([CY SALES])}

-Compare the monthly max to the daily sales value

IIF( . . . , ([Order Date]),NULL)

-If they’re the same, return the Order Date, otherwise return NULL

MAX(int( . . . )) +2

-Turn that date into an integer and get the MAX, and add 2

From my work in Excel, I know that dates are just numbers, they changed to numbers on me all the time. But I didn’t process that Tableau would do the same thing. So then I went to the Number Format, and set it to a date format in Custom:

Lo and behold, it works!  Sorcery! So then I did the same thing for WORST DAY (just using MIN instead of MAX), and I had everything I needed but the color.

I added Measure Values to Color, and then needed to get separate legends for each measure:

For the metrics that were black, I used a Custom Diverging, with Stepped Color of 2 steps, and both sides Black:

I did the same for the Red/Greens, except with Red/Green on the colors instead of black:

The dates were a little different, as I wanted each column to be red OR green, so I set the Start to be -1, and Center to be 0:

With the legends in place, I turned tooltips off, and set the workbook font to Tableau Semibold, and we’re done!  And it’s not a bad looking text table 😉

Click to view on Tableau Public

Workout Wednesday 2021 – Week 4

This week’s challenge is a throwback to a challenge from a couple years ago, but with a new functionality wrinkle to try out the new Map Layers in Tableau 2020.4. Seems like Sean is often coming up with challenges to help with learning new functionality in Tableau, which is awesome. The challenge itself was fairly simple, particularly because of the great work from Tableau devs in creating Map Layers.

I started off adding Longitude (generated) to Columns and Latitude (generated) to Rows. Then I added State to Detail for the filled map portion. After that I dragged City into the view, and a nifty new thing popped up:

I dropped city on, and now I have two Marks Cards:

Now, with City and State in the view, I added SUM(Profit) to Orders.State Color, and matched Sean’s Red-Black diverging palette. Then I added SUM(Profit) to Size for Orders.City. But since we’re adding color to show positive/negative profit, we need the scale to start from zero, rather than the smallest number:

Now I need a calculation to identify whether each city is profitable or not. So I just created a boolean IIF statement:

Then I added that to Color in Orders.City, and matched Sean’s colors, including a white border.

I struggled for a few minutes to figure out how to make the filled states not selectable. I tried turning off the tooltips, but you could still hover on a state and have it highlight the state border, which Sean’s version wasn’t doing.

After putzing around for a bit, I came across a dropdown for the layer, that magically had Disable Selection as an item:

Magic! Next I worked on the bar chart. I noticed that it was showing the City and the State, so I needed a calc to combine the two:

The State Abbreviation here is the calculation Sean shared with us. So then I added City State to Rows, SUM(Profit) to Columns, and City Profitable to Color.

For the tooltip on both the map and the bar chart, I needed a Negative Profit value and a Positive Profit value, in order to make the negative and positive numbers different colors.

Now I can add these to the tooltip, next to each other without a space between, with the Negative value colored red, and I’ll get one or the other depending on the value for the city:

(This is the same tooltip for both worksheets)

Once I got everything on the dashboard, here’s what that layout looked like with my containers:

Then I just needed to add the highlight action so when you hover on the bar it highlights the city on the map:

And we’re done!  I was truly amazed with how simple and intuitive it was to use map layers, and glad Sean gave us this opportunity to check them out.

Click here to view in Tableau Public

Workout Wednesday 2021 – Week 1

It’s been a couple months, after a broken pinky (causing typing difficulty) and the holidays, I’m ready to start the new year. This week’s challenge is from Candra, one of this year’s new coaches. Taking it back to straight table calculations, which is always a good exercise for those of us that tend to reach straight for the LODs.

I started out creating the line chart, with Year on Columns and SUM(Food insecurity) on Rows, and changed the line color to black. I gathered from interacting with Candra’s viz that the selected year and which year to compare to were parameters, so I created those next. One as an Integer, with the values added to the list from the Year field.

The other as a String with the three values identified.

Then I created a boolean to identify the year based on the parameter selection:

Then I created another to identify the comparison year:

I used WINDOW_MIN and WINDOW_MAX to get the first and most recent years, and the Lookup Year to get the previous year to the year selected:

With the two years identified, I decided to try a dual axis to add the dots to the line chart. So I created a calc to give me the SUM(Food insecurity) if the year was Year Selected or Comparison Year Selected:

Then I added that to Rows and set the Dual Axis and Synchronize Axis. When I add Year Selected to Color for that mark, I get a T/F so I set the color accordingly.

To format the numbers, since the requirements said not to add the % with a calc, I remembered the Suffix option in the number formatting:

After formatting I did a quick setup of the tooltip:

The last thing I needed was the variance between the selected year and the comparison year. So I created a calc for the selected year value and the lookup year value:

With these two values, I could then create a variance calc. I used WINDOW_MAX in order to get just the values:

That was the last piece I needed to create the title. Since I needed to include the Selected Value and the Variance I decided to use the worksheet title:

After this, I added the sheet to the dashboard, and then floated the parameter dropdowns into the spaces in front of the Selected Year and the Lookup Year parameter values.

As an added bonus, I decided to add a parameter action that would change the Selected Year when you click on the line chart:

Then I added a TRUE/FALSE filter to automatically deselect the year when clicking on the line:

And we’re done with the first challenge of the year!

Click to view in Tableau Public

Workout Wednesday – Week 44

This week’s challenge from Lorna was a bit of a respite from recent weeks. Thankfully, I’m familiar with building waterfall charts, so it was pretty straightforward.

I started by filtering YEAR(Order Date) = 2020, and also added YEAR(Order Date) to Columns in order to get the 2020 header. Then I added QUARTER(Order Date) to Rows. The hard part was getting the month. Initially when I put MONTH(Order Date) it still put all 12 months horizontally with a bunch of gaps where the quarter and month didn’t match. So I went back to my Week 42 post and found the calc that put the months into 4 columns, and adjusted it to be 3 columns.

This gives me the remainder when the month value (1-12) is divided by 3. I subtract 1 so March doesn’t end up in front of January (3/3 = 0 remainder). Putting that on Columns then got the months in the right place, so it was time to get the waterfall.

I added DAY(Order Date) to Columns, and set the mark type to Gantt Bar. There were some days missing, and Lorna’s requirements were to have something for every day. So we have to check Show Missing Values:

I added SUM(Profit) to Rows and created a quick table calc of Running Total.

In order to get some bars, I added SUM(Profit) to Size. But that gave me a bar going up from the running total value, rather than up to the running total value.

So I edited the SUM(Profit) on Size, and added a – in front of it, and that fixed the bar direction.

You may be thinking, “how’d you get those colors, Kyle?” Normally I probably would’ve done something like IIF(SUM(Profit)>0,’Blue’,’Red’). However, I recently read a blog post about using booleans instead of creating values like that for better performance. So I tried it:

This gave me three values, True, False, and Null. I added it to Color and set the colors to match Lorna’s, with gray for Null.

I also needed to get the monthly total on the far right, so I went to the Analysis to Add All Subtotals:

…and hallelujah it did exactly what I was expecting/hoping it would.

As I checked the tooltip, I realized we needed the actual Order Date, so I added that to the Detail. But then it screwed up my running total table calc. So I then tried adding ATTR(Order Date) instead, which allows you to exclude it from a table calc, and that worked! I also needed to add SUM(Profit) to the Tooltip, since I only had a running total or a -SUM(Profit) in the view. Here’s what the final tooltip looked like:

The hardest part of this week’s challenge was figuring out how to get the month labels in the top left. I tried reference lines and a couple other things, but finally settled on an LOD:

I needed something to give me the highest monthly profit. So I used a FIXED on the month DATETRUNC, but then I also needed a FIXED MAX to get the largest monthly profit value. That also meant I needed to add the year filter to Context. Once I had this value, I added AVG(Label Value) to Rows, and made it a Dual Axis. Then I set that mark type to Line, and turned the Size all the way down along with setting opacity to 0%. After I created the Label Value calc, I realized I needed the monthly profit value for the label.

Then I added that to Label, along with MONTH(Order Date).

Then set that to be on Line Ends, and Label start of line:

With the monthly profit labels in place, we’re done!

Click to view in Tableau Public

Workout Wednesday – Week 43

If there’s one thing I’ve learned doing Workout Wednesday, it’s that I can look at something and think, “oh, that should be easy, I know just how to do it” and be COMPLETELY wrong. This week’s challenge from Luke was no exception. The main guts of the challenge was pretty straightforward, but there were a couple little formatting things that just didn’t quite work as I expected.

I started with the bar charts. I placed MONTH(Order Date) on Columns, and SUM(Sales) on rows. I added the mark labels and rotated them:

My problem came when I tried to format the axis to match Luke’s, with the tick marks every 2 months and in the center. I tried making it continuous and that got the tick marks in the middle, but I couldn’t get them every other. Then I tried using a custom date instead, but that worked about the same. Even when I could get the every other tick mark, I had an extra D in front of January. It was really bothering me, so I checked out what my other WOW friends came up with, and came across Annabelle Rincon‘s solution:

This took care of the extra D before January. Then I duplicated the sheet and changed the metric to each of the other three. With the bar charts out of the way, time for the KPIs.

I knew I would need a parameter to get the KPIs to show the bar chart. At first I created a String parameter with a list of the four metrics, but then I realized that if I’m passing a value into the parameter, I don’t need to set a list.

I started with a MIN(1) in Columns to get a bar, then added a MIN(0) on a dual axis. That allowed me to add SUM(Sales) as a label on one bar, and the metric label on the other. I needed a calculation for the + and – based on whether the KPI was selected. I noticed that  the + and – were bold, while the metric name was not, so they needed to be separate.

For the other part of the label I just created a calc with ‘SALES’ (or the other respective metrics). Then I setup the parameter action, passing ‘SALES’ to the parameter, and Set value to ” when the selection is cleared. However, once I got things setup in the dashboard, my unhighlighting made it so there was no selection to clear, so it wouldn’t deselect. It was doing some crazy things, with the labels disappearing or turning to null, and I couldn’t figure out what was going on. So I changed MIN(1) to AVG(1) and that seemed to fix the labels from disappearing. After 30 minutes of messing with the actions, I went back out to the WOW friends, and found Samuel Epley‘s solution via Donna Coles. Instead of using the parameter action to clear the value, it uses a calc to set the blank value if the parameter already equals ‘SALES’.

So I added that to Detail, and adjusted the parameter action to use that field, and that magically fixed the actions.

I mentioned previously the unhighlighting in the dashboard. I added a calc with ‘FALSE’ to the detail, and set a filter action in the dashboard (I’ve mentioned this method various times previously):

In the dashboard, I needed a vertical container with a white background for the title, and then I added another vertical container with a gray background for all the KPIs. I set the inner padding on the gray container to 15 px. For the KPIs other than Sales (Sales already had top padding with the container padding), I added 10 px to the top outer padding. For the bar charts I added 20 px of inner padding for everything except for 10 on the bottom.

To get the bar charts to only show when their KPI is selected, I just created a boolean for each metric and added to the filter shelf:

The last little thing I noticed in Luke’s challenge was a gray line dividing the white title background and the lower gray background. For that it’s a quick blank object into the vertical container, set with no outer padding, a darker gray background, and edit the height to 1 px. And with that we’re done, with all the little tiny formatting intricacies and all.

Click here to view on Tableau Public

Workout Wednesday – Week 42

September was kind of a crazy month for me, which meant my Workout Wednesday solutions and/or blog posts didn’t happen. But I’m back at it this week, and what better way to come back than have a challenge from the original Workout Wednesday master, Andy Kriebel.

Right off the bat I knew we were in for some fun when the requirements said no LODs and it would have to be blended data sources. Blending seems to always screw up the way I want to do things, so that’s fun… :oÞ  But I got it started setting up the individual data sources.

The first thing I worked on was the daily view. In order to make sure we have all the dates even if there was no activity, we need to use the Date field from the Calendar dataset. So I added DAY(Date) to Columns. Then SUM(Hours) to Rows and set the mark type to Bars, and that gave me a general starting point.

I knew I would need a parameter for the year, so I started by created a custom date for the year:

Then I clicked on the custom date >> Create>>Parameter. After that, I needed one more calc to compare that parameter value to the custom date value for a T|F filter:

I tried a few things to get the columns and rows working to get the months spread into the calendar-like view. I even went back to a method I got from Andy a few years ago that’s usually how I setup a trellis/small multiple. But then I realized it used an LOD, so that was a no-go. I finally settled on this:

For the columns, I took the month value (integer 1-12) and took the remainder when dividing by 4. That’s the ‘%’, and it has a name, but I don’t remember what it is! That got me close, so I needed to subtract 1 from the month value, and that got the months into the right columns.

For the rows, I tried to use division to do it a bit more dynamically, but ended up doing something a little less complex:

So if the month value is less than 5, set it to 0, if it’s less than 9 (but greater than 4), set it to 1, otherwise set it to 2.

Once I add that to Columns and Rows, that gave me the general structure. I made some adjustments to the size of the bars, turned off borders and set them to Black. I noticed after I finished everything else, that I had failed to figure out how to show 0 hours when there was no activity. Because we’re blending the data and activity data sources, it took a slightly different calc than I would otherwise do:

I decided to compare the Date to the Date Time from the Activities data set, and if that’s present, give me the SUM(Hours), otherwise give me 0. I actually didn’t have a ton of hope that this would work, but it did!

For the month labels with monthly hours, I felt like I needed a dual axis of some sort, but couldn’t put my finger on how to do it. I was thinking it would be on the date axis, but that didn’t really work. I was pretty stumped, so I took a gander at Sam Epley‘s solution for this week, and had an “aha!”

At first I used LAST()=0, but then realized that put the text horizontally in a different place depending on which month it was (28, 29, 30, or 31). So then I just went with DAY(Date) = 28 so it would be consistent. I picked 13 just based on the spacing I saw in Andy’s viz. So I added that to Rows, set the Dual Axis and Synchronize Axis. Then you set that mark type to Text and add the month and hours. Except the month abbreviations were in uppercase on Andy’s, but not mine. So I created this one:

We also need the sum of total hours for the month. So I added a WINDOW_SUM(Clean Hours) to text, and that gave me what I needed. With those to pieces in place, here’s what my text looked like (being sure to right align):

I also noticed as I compared mine to Andy’s that my bars were right up against the dividing lines (which I formatted to the widest setting and white), while Andy’s had space on both ends. Once I changed the DAY(Date) from Discrete to Continuous, I magically had space! With that we’re finally done with the main part of the workout.

Moving on to the weekly bars, it was pretty straightforward. With the Year Filter on, I added WEEK(Date) to Columns and SUM(Hours) to rows. After changing the mark type to Bar, I just needed to remember to set the tooltip:

*** I noticed that my bars didn’t quite match Andy’s. As I dug in, it looked like Andy’s might’ve been looking at weeks starting Monday where mine were weeks starting Sunday. I tried creating a DATETRUNC to set it on the week starting on Monday, but that gave me the actual date, and formatting the week number gave me week 53 instead of week 1. So I’m thinking it’s a situation of Andy being in the UK with a Tableau setting of weeks starting on Monday. At least, that’s what I hope is the difference. ***

The last worksheet (requirements said max of 3 sheets) had the BANs. I went with ol’ reliable…MIN(1) (x3) I added MIN(1) to Columns three times. Then set all mark types to Text, and added SUM(Hours) to the first, SUM(Miles) to the second, and COUNTD(Activity ID) to the third. Then set the text for each one with their metric label (hours, activities, miles) and changed hours to blue. Turn off the tooltips, and we’re set with this one.

It’s always hard to go through setting up the dashboard, so here’s what the final layout organization looks like:

And we’re done!  After struggling through the last few weeks, it felt really good to get this one done!

Click here to view in Tableau Public

Workout Wednesday – Week 34

This week’s challenge from Lorna changed it up a bit with some mapping stuff. I don’t get much of a chance to use mapping at work, so it was kind of fun to play with something new.  Since I haven’t used the functionality much, I used a couple posts from Jeff Shaffer and Sean Miller that helped a bunch to figure out the radius setup.

I started with a parameter for the location selector for Buckingham Palace or St Paul’s Cathedral:

Then I created a couple calculations to set Latitude and Longitude for the center point based on the parameter selection:

Last step is use MAKEPOINT to create a point for the center location:

With both points, I can now measure the distance from the chosen point to the crime point:

Next I created a parameter for the number of miles radius:

With that, I can create a boolean to filter the items to only within the radius:

Now we can put the map together. Double-click on Latitude (generated) and Longitude (generated), and that inserts them on Rows and Columns. In order to get each dot, we add Crime ID and Crime Point to detail. I also added Distance to Color. Initially, when it was continuous, I couldn’t get the Hue Circle color scheme. So I tried changing it to Discrete, and selected Hue Circle palette, and Assigned palette. That matched up with what Lorna’s looked like. I wasn’t quite sure what her note about color bands in 10 meters meant, as I was unable to create bins or anything from the Distance field.

In order to get the larger dot, we need a dual axis. Ctrl+click on Latitude(generated) and drag it next to the original. Then right click and select Dual Axis. Then on this second Marks card, you replace Crime Point with Location Point. Remove Distance from Color, and set it to White. Then you can increase the size.

Last thing we need for the map is to adjust the map settings. From the Map menu, click on Map Layers. Set Style to Dark, and then I set Washout to 25%:

Now on to the Tooltip. Add Month to Columns, and COUNTD(CrimeID) to Rows. Make sure the Distance Filter is applied to this sheet as well.

Back on the Maps sheet, we need to add COUNTD(CrimeID) to Tooltip, and then setup the tooltip:

The important thing when adding the Tooltip sheet is to make sure it only filters on LSOA name.

On the dashboard, we pull in a Vertical container, and add the Map to it. For the title, we need to include the two parameters:

We can then add the two parameters to a floating Vertical container, and place them in the top right of the view. And we’re done!

Click here to view in Tableau Public

Workout Wednesday – Week 33

Ann‘s challenge this week was to create a connected scatterplot with some automatic insights. I totally went down the wrong path, so it took me a bit longer than it should have. But we figured out two different ways to do things!

I started with the scatterplot. I placed SUM(Quantity) on Columns, and Profit Ratio on Rows. Then I added Category to Color, set the colors per Ann’s viz, and set the mark type to Circle. Next, I created a custom date from Order Date:

This just made it easier to use in the Detail and calculations. So I dropped that on Detail, and now I have all the dots I need.

For the connected part of the scatterplot, I did a quick search and found Ryan Sleeper’s blog post about how to build one. The variant here is I only need it to connect when something is selected. So first I created a set from Category, and then created a calc to give me the value if the set was true:

Now I can take this calc and add it as a dual axis on Rows (since I set it to be Profit Ratio). Then I want that mark to be a Line, with Category on Color again, and then the Order Date (Months) on Path and Label. The last thing we need here is the Set Action to make it work:

At least I thought that was the last thing. But when I clicked on dots, while the line would show up, only the dot was highlighting. So in addition to the Set Action, I needed a Highlight Action:

With these actions in place, just need to clean up the tooltip, and this sheet is complete:

As I was starting down the Insights path, I started out using LODs. But I kept running into issues with aggregation or something. So we decided to go down the table calculation route. And we got it to work, with the exception of the INSIGHTS title. So I’m going to go through what we did, just as a learning exercise, even though it didn’t quite match close enough to Ann’s version for my liking.

I started with ranking the Profit Ratio and Quantity:

Then used these ranks to identify the desired month values:

So if the rank is 1 (top) or equal to SIZE (bottom), then give me the bullet point plus the string of the month.

Then we did a similar thing for the text in between the month and the value:

This time instead of OR, I needed nested IIFs because each text line is different.

Lastly, we need the value for each one:

To get a clean percentage on the Profit Ratio, we multiply the PR by 100, then take the INT, and add the percentage in our ending string.

Now we add all three of these to Text, and setup the label:

Each of the table calcs needs to be set to process on the Order Date (included the nested rank calcs in each):

In order to get only the 4 items we want to show up, we can create a Rank Filter:

Then we can create a variant on this calc to create a ranking order to get them to appear in the correct order:

When we add this to Rows, it has a Null followed by 1,2,3,4. So I excluded the Nulls, unchecked Show Header, and we have our insights:

The issue with this solution is that we couldn’t get INSIGHTS to follow the color scheme without adding it to the Label, but then that messed up the spacing for each of the rows. But aside from that, this solution gather the information we were looking for. (I didn’t bother formatting the dates in this portion)

Now for the other solution, which actually did use LODs. I think I’ve gotten so used to having to figure out Table Calcs for WOW that I just dismissed my LOD solution prematurely. So once I stepped back and thought through it (see also: peak at Ann’s sheet), it made total sense.

We start with the Profit Ratio and Quantity by Category and Month:

Then you add the MIN and MAX of each to Text. Next we need four different calcs for each of the four months we need. These calcs check to see if the Category Profit Ratio or Quantity equal the MIN or MAX fixed on the Category, and return the Order Date (Months) if they do:

Then we add all of those to Text,add Category to Color, and the Category Set to filter, and set up the Label:

This was definitely the more simple solution for this Insights, even though it involved a few more calcs.

In the dashboard, I set things up in a Vertical Container (title, black bar, sub title, scatterplot, footer):

Because of the location of the Insights sheet, it needed to float. (I also floated Sheet 2, in case anyone wanted to see how that sheet worked. But in order to hide it, it has to be on the dashboard. So I set it to 1 x 1 size floating in the left corner.)

And we’re done!

Click to view in Tableau Public

Workout Wednesday – Week 32

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