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