Month: March 2020

Meera‘s first challenge  was a great practice with date calculations. At first glance I also thought “oh, easy layout” but then I read the second requirement to only use 3 sheets. Not quite so easy, but still doable.

I started with the Sales bar chart. MONTH(Order Date) on columns, SUM(Sales) on rows. I filtered the YEAR(Order Date) to 2018/2019. Then I added YEAR(Order Date) to color and size, and matched the color to the requirements. In order to get the bars to be in front of each other instead of stacked on top you have to turn stack marks off in the Analysis menu:

As I formatted the axis, I realized that my sales value in the tooltip would be formatted the same way, which would be contrary to the requirements (50K vs $50,000). So I duplicated Sales and placed SUM(Sales (copy)) on tooltip. I had the same kind of issue with the date in the tooltip after formatting the axis to be the first letter. So I created a custom date on Order Date and added it to my tooltip:

I finished up the tooltips before the end of the process today, I was feeling wild and crazy.

The last thing I needed was to filter through Mar 24 for the current year. So I created a boolean filter:

That finishes off my sales chart. I duplicated it, switched Profit with Sales, adjusted the tooltip, and the bar charts are done!

Now to figure out how to put two separate KPI sections in one sheet…I needed five calcs  for MTD, PMTD, MoM, Run Rate, and YTD. In order to make those, I created Current Month and Prior Month:

Here’s each of those for Sales:

MTD

PMTD

MoM

Run Rate (Divide MTD sales by number of days, and multiply by number of days in the month, found by adding one month to today’s truncated month, and subtracting one day)

YTD

Once I had all these calcs (and the accompanying Profit ones) I added Sales and Profit to rows, which gave me Measure Names. Then I did a dual axis with MIN(0.0) and added all of the calcs to the label. The dual axis allowed me to put the profit calcs in one mark card and the sales calcs in the other. Formatting, adjusting axis, and drop them in to the dashboard.

I’m all about containers (I think I’ve mentioned that before) in dashboards. I think the easiest way to show the build is with the item hierarchy:

In this case, I do have a floating blank object. That’s for the dividing line between the Sales KPIs and the Profit KPIs, to match the dividing line between the two bar charts. I played around with the column borders, but that left some borders at the top and bottom that just didn’t quite match up. So I floated a blank in the right spot.

And that’s it!  As I took a look at Meera’s, there were a few differences in how we did things, but in the end, we arrive at the mostly same result. (I did recognize she probably did a dual axis in the bar charts in order to achieve some transparency of the PY bars, but I had already gone down my path and didn’t feel like changing it)

Click here to view on Tableau Public

 

Lorna‘s challenge this week was a good mix of parameters, set actions, date manipulation, and container magic.

To start I set up the period selector parameter:

Then I created a date calc that would dynamically change the date grain based on the parameter selection, using DATETRUNC:

I dragged Date to the columns shelf, selected DAY(Date) [Continuous], and made sure Show Missing Values was checked. Then changed the mark type from Automatic (line) to a Bar. Add SUM(Profit) to color, and we have the basic workings of the bar chart.

I created another parameter for the number of years to display. This was an integer parameter with a range of 1-4:

Then I created a calc to compare my date calc to the difference between 2020 and the number of years selected:

Date Filter = True on the filter shelf, and we’re moving along. To adjust the size of the bars (which I admittedly put off until last, but will include here with everything else from this sheet), I used another case statement based on the Select Period parameter:

I played around with the numbers here until they looked about like Lorna’s. (A slightly more dynamic solution by Rody Zakovich can be found here)

Drop Size onto Size (and changed aggregation to MIN, rather than SUM), and we’re done with the bar chart! (assuming axis formatting and tooltip adjusting)

Then I needed the Subcategory list to run the set action on. This is kind of second nature at this point after all the workouts this year. Subcategory to Rows, MIN(0.0) to columns, axis range set to -0.05 and 0.5, Shape mark type (that gives me blue open circles). Changed the color to black, and then created the Subcategory Set so I could put that on Shape:

Then I created the set action (Worksheet>>Actions):

If you select the data source under Source Sheets, rather than the sheet you’re on, you won’t have to recreate the action when you apply it to your dashboard. Then, in order to deselect the shape when it’s clicked on, I used my now tried and trusted TRUE/FALSE method from Yuri Fal. A quick edit of shapes to change the IN to filled circle rather than open square shape, and we’re ready for dashboarding.

When I build dashboards, I always use containers. ALWAYS. But, where I might deviate from others, I leave one Tiled container at the top. Otherwise every time I add a chart to the dashboard, it seems like Tableau adds another Tiled container to put the legends in. So I keep one, and then run Vertical container inside of it. Here I put my Bar chart, the dashboard title (with Select Period parameter so it’s dynamic), and my footers (which are all inside a Horizontal container at the bottom of the main Vertical).

My new favorite thing is the super easy menu functionality. I’ve done other workarounds where you have two dashboards, and it’s a big pain. This is seriously so simple, I can’t get over it. When my legends/parameters popped up to the side of the bar chart, I pulled in a floating Vertical and added the two parameters and the Subcategory sheet. Then you just click Add Show/Hide button in the container dropdown:

Fine tune the placement of the icons and the container. Add a white background for the overall Vertical container, as well as the dashed border. Final testing and we’re all set!

Click here to interact on Tableau Public

Ann’s challenge this week was a bit of a respite from the last few weeks, but still packed plenty of little punches.

The challenge revolved around a selected date, so I started with that parameter.

Then I needed to figure out which weekday that day fell on.

With the weekday identified, we need two filters. One to identify the previous 11 weeks and another to identify the dates within those 12 weeks.

Once I verified these got me the right dates, it was time to create the rank tables. First, I did a RANK on SUM(Sales). However, later on, I realized that didn’t match up with Ann’s viz, particularly on orders and quantity where there were ties. So I went with RANK_UNIQUE instead of just RANK. Then to find the average line, WINDOW_AVG on SUM(Sales). In order to create the Above/Below Average categories, I needed to compare the sales to the average:

For the color, I needed to identify the top and bottom as well as whether it was above average:

The last thing needed for the table is the arrow to identify the chosen week. At first I thought it could be a shape, but then realized I wouldn’t be able to put a shape in the dimension grouping. So I copy/pasted one for the calc:

With SalesColor on Color, the nice colored boxes turn to little squares. 🙁 So I played around with a Gantt chart. I added MAX(1) to columns, and MIN(-1) to size, and fixed the axis to 0 to 1. With everything in place (including the SalesHeader, which I’ll get to shortly), here’s what it looks like:

(The Sales Rank header is hidden, and you have to right click on the SalesAbove/Below header to rotate the label. Format for row banding at the SalesAbove/Below level. )

In order to include the rank for the chosen week in the table header, I needed to identify just that rank value:

With the Sales table in place, I copied each of the calculations for order and quantity, duplicated the worksheet, and replaced accordingly.

With all three tables ready, I dropped them each into a vertical container with a blank below for the gray shadow. Turn off the outer padding, and fix the height at 2 pixels. I then dropped all three verticals into a horizontal container.

One thing I ran into when I tried to get the titles for the tables to be white despite the gray background. Doing so left a sliver of a gray line between title and the table. However, clicking on the sheet and setting the background color to white from the dashboard layout pane worked like a charm.

I started to add the title for the dashboard, and realized in order to include the weekday in the title it needed to be a worksheet (hence Ann’s requirement for no more than 4 worksheets). So I used a trick I picked up in week 4 just just add ” to columns and text for a blank sheet. In order to get the weekday to be in uppercase, I needed one more calc with UPPER(Weekday). Then my worksheet title looked like this:

Once again I put the sheet in a vertical container with a blank underneath. In order to not show the big blank worksheet, I fixed the height at 34. I had to play around with the padding and height a bit after I published to Tableau Public because it rendered a little differently than in Desktop.

Then I put this vertical on top of the horizontal container with the three tables in one more vertical.

The finishing touch was the calendar icon showing the date parameter entry. I learned this last week, just add the parameter in a floating vertical container, right click and select Add Show/Hide button. Added the icon Ann linked to for Show, left the X for Hide. And we’re finally finished!

Click for Tableau Public

Sean‘s Week 10 challenge (despite calling it week 9) was a great opportunity to play with some new features in Tableau 2020.1, and reinforce some of the functionality I’ve picked up from some previous #WOW2020 challenges. I was able to complete the Intermediate challenge in our live session today, and got started on the Jedi challenge, which I then figured out the rest of the way tonight. This challenge was kind of fun, since I don’t often use maps at work, so I got to play around with something different. My ability to finish the intermediate challenge in a little over 30 minutes speaks to the amazing work by the Tableau devs in making BUFFER, MAKEPOINT, and DISTANCE such easy functions to use, since I never used them before. (Also, the blogs Sean shared from Zen Master Marc Reid and Tableau developer Filippos Lymperopoulos were great intros on how to apply these functions, and directly led to my being able to do the following work)

Intermediate Challenge

I started off just testing out a BUFFER calculation on the London Hotels data source. Pretty easy when you have Latitude/Longitude for your data points.

The MAKEPOINT converts your Lat/Lon into a point on the map, and then you tell the BUFFER how big of a radius you want (500) and what unit of measure (in this case meters, or ‘m’). Once I saw how that worked I went back into the data source viewer and joined the London Pubs csv using an Intersects join:

Here we’re basically joining points on the map where pubs exist within any buffer areas around the hotels. By doing an Inner Join we remove the hotels that don’t have any pubs within the 500m radius.

**SIDENOTE: New sometime around 2019.4 is a whole slew of additional background maps. Here’s the list of options in 2019.2:

And here’s 2020.1:

Great options for those of us who have yet to dip our toe in the pool of Mapbox. **

When I went back to my map, I was trying to play around with getting the pubs to show, and struggled for a minute until I realized I was only showing the Hotels LAT/LON in the view. After I joined Hotels and Pubs, the Latitude (generated) and Longitude (generated) showed up in Measures. Once I dropped those into Columns/Rows, I was in business. In order to get the buffer for hotels and the circle marks for pubs, you have to use a dual axis. This is easily done in maps by Ctrl+clicking on Latitude (generated) and dragging it next to the current one, then selecting dual axis from the pill dropdown:

In order to apply the buffer to your view, you just drag your Buffer calc to Detail on the marks card. I also added Hotel Name to Detail, and COUNTD(Pub Name) to Label, which gave the bold numbers within the buffer (once I adjusted the font size and made it bold).

I then needed to calculate the distance between each hotel and pub within the radius. This is just DISTANCE between two points, and the unit of measure:

Once I had the distance calculated, I could create the points on the map for the pubs:

(I created a Radius parameter, which in this case is set to 500) I then added this calc to Detail, along with Pub Name. I switched the mark type to Circle, but the circles weren’t as big as Sean’s were, and wouldn’t change as I adjusted the size. So I hacked it with MIN(1) on Size. That still gave me some trouble so I adjusted the size settings:

This allowed me to adjust the size of the circles to match Sean’s. Then I matched the colors and opacity. That just left me with the tooltip.

The tooltip was just a basic table with Pub Name and distance from hotel. But since it was showing up on the buffer around the hotels, Pub Name wasn’t an option for the tooltip filter. So I added Hotel Name to the detail, and filtered the viz in tooltip on that:

Drop everything into the 700×700 dash, and we’re done!

Jedi Challenge

I was excited to combine this newly found “skill” with some others I had gained previously when I looked at what the Jedi challenge was, so I jumped into it.

I started with the table just because I knew it would be fairly straight forward. After adding Name, Yelp Rating, Price Rating, and SUM(Yelp # of Ratings) to the view, I realized I would need something to sort the hotels based on the selected option. So I started with the parameter:

Then I created my case statement:

The Price Rating was kind of weird, because the Null for Great Scotland Yard Hotel was showing up at the top, rather than the bottom, plus the other two ratings needed to sort Descending, while the Price Rating needed to sort Ascending. So I turned the Null into 5 using the IFNULL function, and made Price Rating negative. Then I added Sort to the far left of the Rows shelf, and sorted by Sort and Name (Descending for both).

I also added the Yelp # of Ratings to label for the Min/Max at the Table scope. In order to keep the axis label of #of Ratings, but not have the axis, I just formatted the axis to be a white font, and then adjusted the title format to be black.

I also created a set on Name. Then created a Set Action to change the set to the selected hotel:

Once I had that, I could drag Set 1 to color, change the blue to red, and my table was ready to go.

Then I jumped into the map. I used the same basic dual axis principles from the intermediate challenge, but this time, in order to just get the selected hotel, my buffer calc looked like this:

Hotel Location was kind of tricky, because I needed it to be kind of a constant in order to calculate the distance from the pubs. So first I found the individual Latitude and Longitude for the hotel in the set:

And then I used an LOD to make the point for each data row:

I could then use that in conjunction with Pub Location:

To find the distance between the selected hotel and each pub:

So one marks card had the Buffer in Detail and Name on Label. The other had Pub Location and Name on Detail (I forgot Name, and the distance calc wasn’t working for me and I couldn’t figure out why the heck not…til I realized I wasn’t giving it a pub to calculate the distance for…oops!), with AVG(Distance) on Color and Size. I adjusted the color and played with size to match Sean’s viz, but needed one more calc in order to include the selected Hotel in the tooltip:

With map and table in hand, all I had left was the selector sheet and some actions. The selector sheet was very similar to Week 4, so I won’t belabor it here. I made one calc for the three options:

and another for the shape:

Throw in TRUE and FALSE for the auto deselect and min(0.0) [(both previously mentioned here various times)], and we’re ready to hit the dashboard.

I had no idea how to do the appearing/disappearing container for the selector/table other than it would be a floating vertical container with the Selector and Table sheets. But I knew Lindsey Poulter blogged about how she did a similar thing in her Iron Viz. And the magic is remarkably simple…click the dropdown arrow on the container you want to show/hide, and then:

That brings up this nifty little window where you can choose an image or a text button, and what they look like when it’s shown or hidden:

I really was expecting this part to be more complicated, but it wasn’t!

Added the legends and buffer radius parameter entry, some final testing, and we’re all done!

I’ve really loved Sean’s challenges as a guest host the last couple months. They’ve really helped me dig into and learn great new functionalities in Tableau that are directly applicable in my work projects. Well done Sean!

Tableau Public link here