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!