Workout Wednesday – Week 15

I feel like Ann‘s challenges always end up reminding me of some little trick in Tableau that I try and solve with some elaborate calculation magic and it just a little formatting thing.  This week’s challenge was no different. I’ll go through the back-flippery I went through and then show the “easy way” (read: the right way), but I always like to document my thought process.

I started with the two parameters I would need. Pretty basic date and integer parameters:

I knew I would need to identify the prior week for highlighting as well as the number of weeks prior for filtering the view. For the prior week I just want the order date between the parameter value and 7 days prior to the parameter value:

For the filter, I tried doing a  DATEADD using ‘week’ but it took it back to the Monday of that week, which didn’t quite work. So I had to go by ‘day’ and multiply the number of weeks by 7:

I did some checking along the way with these to make sure the proper number of days were showing up. Then I dragged Order Date to columns and chose WEEKDAY as the date aggregation, and SUM(Sales) on the rows. That gave me a great view of Sunday-Saturday. But I needed to figure out how to get them to sort based on the ending date. I started trying to compare the weekday value of the ending date to the weekday value in the view, but then realized that just made numbers in the same order but some greater than 0 with others less than 0. Then Yu (who I decided should have just run the screen sharing in our session today, since she had most of the answers) suggesting using a modal, which gives you the remainder when dividing by a number. So we did a DATEDIFF between Order Date and the ending date, and subtracted that modal value from 7:

So we dragged that onto columns, and it put things right in the perfect order…but it wouldn’t draw a line because of the multiple dimensions!

 

I spent about 5 minutes trying to figure out different ways to get the numbers mashed up into the right calc to just return the days in the right order, and then I realized we just needed to sort the weekday by that Sort value:

Magic!  Lines!

**SIDENOTE: To get multiple lines in the view, I divided a DATEDIFF by 7 to get a week number:

and made it an integer. To get the colors, I added Prior Week to Color. While I was there, I turned on the point markers:

END OF SIDENOTE**

But my prior week is missing a value on Friday. Challenge requirements showed I needed to have 0, not NULL. So I threw a ZN around my SUM(Sales), and that gave me a 0 value on the chart. (ZN stands for Zero Null…this function will turn NULL values into 0, which works great for doing arithmetic when there are null values present so you don’t get a corresponding NULL in your calc)  This is where the back-flippery starts. I then needed to create a calc for the tooltip to say ‘no sales’ when it’s 0, otherwise show the dollar amount, with a comma. Great, no biggie…

Obviously, this one won’t give me a comma. I started to wonder if this was like a REGEX thing or something. Because I’m going through the challenge live with other team members, often if I’m really close I want to give them closure around how the thing actually gets done, so I’ll take a peek. In this particular case, I was looking to see if Ann had used REGEX.

Nope.

It was much more simple, and I’m hoping I will actually remember this trick after learning the hard way again. Back before using ZN, if you just format SUM(Sales) and click on Pane, there’s an oft-forgotten (at least by me) section at the bottom called Special Values:

Special Values allows you to identify text you want to appear instead of NULL (in this case, ‘no sales’). It also allows you to identify how to show the marks. This dropdown defaults to Show at Indicator (the little indicator that shows up in the bottom right of your viz). When I clicked Show at Default Value, it added them at 0. Boom, problem solved!  Almost…

The date doesn’t show up on the null values!! (Duh, Kyle, they’re NULL!) I started thinking through figuring out how to look at the next date or something like that, but again, wanted to provide some closure and at this point I already had Ann’s workbook open. If you’ve read this far I guess I’ll share it with you:

Yeah, I know…what the??? Here’s my plain English attempt:

If the Order Date is not null, use the Order Date – IFNULL(ATTR(Order Date),

If it is null, then if the previous Order Date value +1 is not null, give me that – IFNULL(DATEADD(‘day’,1,LOOKUP(ATTR(Order Date),-1)),

If that one is null two (ie two days in a row without sales), then lookup the next Order Date value and subtract 1 – DATEADD(‘day’,-1,LOOKUP(ATTR(OrderDate),1))

And turn whatever comes back into a date – DATE()

Got it?  Sweet, you’re now a wizard, Harry. Just add it to your Detail and pop it in your tooltip and you’re golden. When I got to the title and the tooltip I realized I needed to name the first and last weekdays. So I did two final little calcs for that:

Final formatting, throw everything on the dashboard, and we’re done!  (Normally I use a text box for my dashboard titles, but since this one need to incorporate these first/last day calcs, I edited the title of the worksheet so those could be included there.)

Click to view in Tableau Public