Month: June 2020

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