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.