Can you swap States?

Donna ColesLeave a comment

Week 49 of #WorkoutWednesday2019 was Luke’s last challenge of the year, so following a poll he posted a ‘notably tough’ challenge.

On the face of it, it didn’t look too bad…. I figured it would involve a trellis chart for the small multiples, set actions (for the state selection), and something table calculation related to crack the ranking. Hovering over the state label for each small multiple, I also figured some dual axis was probably at play. The bit that actually looked most tricky to me initially, was displaying the States just as their shapes.

Single State viz

I decided to build the single state viz first. I created a set (Selected State) based off the [State] field, and selected California as the state ‘in’ the set.

I then started to build the viz simply by double-clicking on State (which automatically adds State to the detail shelf, and the automatically generated Long & Lat fields to the rows & columns. I added the Selected State set to the filter shelf, which immediately restricted the data to California, and I changed the mark type to filled Map.

To isolate the display just to the State itself, I figured would be something to do with the various Map Layer options available (menu Map -> Map Layers); I wasn’t sure exactly what but found by unchecking every pre-selected option, I got a ‘clean’ display.

Changing the opacity of the mark colour to 0 and setting the border to red gave me the desired display.

Maybe the State shape wasn’t going to be as much of an issue as I thought….?

The cities needed to be displayed as circular marks, so I knew this would need a dual axis to make this work. I duplicated the Latitude field (hold ctrl as you click and drag the field) and did the following :

  • changed the mark type of the duplicated field to circle
  • added City to the Detail shelf
  • added Sales to the Size shelf
  • changed the colour of the mark to blue, upped the opacity to 50% and removed the shape border

I then made the chart dual axis and increased the size of the circles to suit.

Finally I added State and Sales to the Label shelf of the Map marks card and adjusted the Label formatting to suit.

Ranking

So the requirement was to show the top 25 states in a ‘grid’ or ‘trellis’ format ordered by the state with the most sales.

However there was a subtlety to this:

  • the grid should always show 25 states
  • the selected state should not display in the grid
  • the overall rank of the state should display, so if the 3rd largest state is selected, the displayed ranking would be 1, 2, 4, 5, 6… up to 26; 3 would be omitted from the list.

The best way to explain how I tackled this, is to show the info in a tabular format.

Firstly, create a table of Sales by State sorting the State by Sales desc

Apply a Quick Table Calculation of Rank to the SUM(Sales) pill. Then edit the table calculation, setting the rank to be unique and fixing the Compute Using to apply over State.

Add the Selected State pill alongside State, and as California is still our selected state, you see we now have two ‘1’s. This is because we fixed the table calculation to State, so its now being applied for each ‘In/Out Selected State’

This is the table calculation we want to use to filter our data to get the ‘top 25’ fields. With ‘California’ selected as being ‘in the set’ and therefore the selected state, we need the next 25 states to the be ones showing in the grid. This being from New York to Oklahoma.

Holding down ctrl and then dragging the Sum(Sales) pill to the filter shelf (ie duplicating the pill), you can set it to show at most 25

However, the ‘rank’ displayed against each row, isn’t the rank we want to show on the viz. New York is the 2nd largest state, so should be labelled no 2, not no 1 as shown above.

We need another version of the Sales rank. Add Sales back into the chart, and again apply a Quick Table Calculation of Rank.

The 2nd rank is now showing values 1 – 26, and if you edit the table calc, you’ll see it automatically has set itself to ‘table down’ which is actually being applied to both State and the In/Out Selected State. Alter the table calc to be unique and fix it to apply to State & In/Out Selected State, which will ensure the values remain the same regardless as to how you move pills around.

This second rank is what is used to display the ‘overall rank.

Finally, we’ve still actually got 26 states shown, when we only want the 25 states ‘out’ of the set displayed. We simply apply the sneaky trick to ‘hide’ the In (click on the ‘In’, right-click and select Hide).

Change the set value to Ohio for example, and re-show the hidden data (click on In/Out Selected Set pill and Show hidden data). You’ll see Ohio is 8th in the overall rank, but is ‘in’ the set so ranked 1 in the ‘top 25’ filter rank.

When I come to build the map trellis later, it is these table calcs and techniques I will have applied.

Trellis Chart

In this instance we have a fixed number of states to display (25), to show in a 5×5 grid; 5 rows and 5 columns. Each of the 25 states we have needs to be assigned a row number and a column number.

Let’s go back to the tabular display to help with this. With the display just showing the 25 States ‘out’ of the set (by hiding the ‘In’), let’s add INDEX() to the view. INDEX() is a table calculation most often used to number rows. INDEX() is set to compute over the State only (so the numbers 1-25 are listed). Note this is giving the same information as the Sales ranking discussed above, and we could reference the same field, but INDEX() is more generic and referenced in many trellis chart solutions, so let’s stick with that.

What we’re looking to achieve, is the first 5 rows listed, to appear in the 1st row, across 5 columns. Rows 6-10 would be in the 2nd row etc etc. I need to build

Cols

FLOAT(INT((INDEX()-1)%5))

This takes the Index value and subtracts 1, and returns the remainder when divided by 5 (%5=modulus of 5). Storing the final output as a float will become clearer later.

Rows

FLOAT(INT((INDEX()-1)/5))

This takes the Index value and subtracts 1, and returns the integer part of the value when divided by 5. Storing the final output as a float will become clearer later.

Adding these on to the table, and again setting the table calculation to compute by State only, you get

and if we shuffle the pills around to create the rows & columns, and keep just the pills we need we get

But it’s all upside-down : we need California top left, not bottom left. We can fix this by editing the Rows axis, and reversing it.

Now by simply changing the mark type to Map, we can get the shape states to show – it’s like magic! You’ll need to increase the size to see them properly.

Side note – this was actually quite a revelation; it took some time for me to get to this, having unsuccessfully had views with Lat & Long displayed (as that’s what a map chart always needs right?), resulting in the state shapes being positioned all over the place. Writing this blog and reproducing steps as I type, has made things seem much simpler, than when I was tackling the challenge initially!

As you can see, things aren’t perfect yet, but we’re on the right track. The axis need editing to extend them. Rows is set from -0.5 to 5, and Cols from -0.5 to 4.5 (this is why we needed to set the field to be FLOATs).

The colour of the mark also needs adjusting to match what we did when building the single state viz.

The label positioning isn’t also right, even if you change the alignment, so move the State from the Text to the Detail shelf and don’t show any labels. Then create an additional axis on the rows by duplicating the Rows field to exist alongside, then ‘type’ into the second instance and change to Rows+0.5

Make this dual axis, synchronise and change mark type to Text. Make sure the opacity on the colour is increased back to 100% and adjust the size of the text.

Now it’s just a case of tidying this view to match the requirements; adding additional fields to the Text shelf, removing axis, row/column lines and gridlines etc.

Once done, both the views can be added to a dashboard, and the ‘select state’ interactivity is achieved using a Set Action dashboard action.

And that’s it. As stated I did have some struggles when building initially, but as most things, it’s down to the path you happen to follow. If I’d initially built based on the order I’ve authored this blog, and the tabular views I’ve built to demonstrate techniques, I wouldn’t have had any problem. But it’s all valuable learning experiences and adds to my understanding!

My version of the viz is published here.

Happy vizzin!

Donna

Can you build a bar chart that automatically combines small contributions?

Donna ColesLeave a comment

Yes you can! By following this guide 🙂

If you attended / watched Ann & Laura’s Speed Tip Session at TC this year, then you’ll have seen how to complete the core part of this challenge. I watched the session after I’d completed it, and found I’d approached it slightly differently from Ann. I’ll walk through what I did.

So the core requirement of this challenge was to

  1. Identify % of Sales per State
  2. Group those with a value less than a threshold defined by the user, into an ‘Other’ pot, and colour the ‘Other’ pot differently.
  3. Sort the display based on % Sales descending, except the ‘Other’ pot should always be at the bottom
  4. Label each bar with State/’Other’ pot and % sales, positioning the label above the bar, and colour accordingly
  5. Add tooltips for each bar, coloured accordingly
  6. Incorporate a summary of the number of states in and out of the group within the chart title (since it all needed to be created on a single sheet).

As I often do with these challenges, I played around with Ann’s published solution; changing the parameter and seeing how the viz altered, and hovering my mouse over various points, to see if it would give me a clue as to how it had been built – in this case I was mainly interested in the labels.

What I found from doing this was there was another mark at the 0 point of the axis; it looked like a gantt bar mark. This suggested dual axis would be involved.

So armed with that information, let’s crack on…

Identify % Sales Per State

Total Sales

{FIXED:SUM([Sales])}

This simply stores the sum of all the sales against each row in the data source

% Sales Per State

{FIXED [State]:SUM([Sales]) / SUM([Total Sales])}

The sum of the sales per State is determined as a proportion of all sales

Group those with a value less than a threshold

A [Threshold Percent] parameter was created to store the user defined variable (defaulted to 3%), and then this was used to determine how the State should be grouped for display

State Grouping

IF [% Sales Per State] >= [Threshold Percent] THEN [State]
ELSE ‘All Other States’
END

Sort the display based on % Sales descending

Whilst the States needed to be sorted with the largest % first, the ‘Other’ pot always had to display at the end. To do this I created a new calculated field

State Order

IF [State Grouping] = ‘All Other States’ THEN 1 ELSE 0 END

With the basic components now defined, we can start to build the chart

The [State Order] field placed at the front, meant that the States are listed first, and forces ‘All Other States’ to the bottom.

Setting the Sort on the [State Grouping] field to be sorted based on the [% Sales Per State] desc, gives the required result:

Colouring based on [State Order] gives the split required too.

Label the Bar

As stated above, a dual axis is needed for this, utilising our old friend, MIN(0) on the columns. The Mark Type of MIN(0) is set to Gantt Bar, with the [State Grouping] and [% Sales Per State] added to the Label, with the font adjusted to Match Mark Colour, and aligned top right. The Colour of the Gantt Bar mark is also set to 0 transparency so it looks invisible.

To make the label appear when the axis is ‘dualled’ and synchronised, requires a bit of ‘jiggery pokery’ with the row height and bar size. I found this to be very much trial and error, even publishing a couple of times, as while it looked overlapped on my laptop screen, the view did render ok on public.

Note – what I found with this solution is that changing the parameter could make the labels overlap again, which wasn’t ideal. Ann’s solution also suffered from this. I’ve been trying to figure out an alternative, but haven’t got there yet.

Tooltips

The tooltips on the bars look straightfoward, but the State ‘label’ is coloured based on the bars, so requires a couple of calculated fields

Label : State

IF [State Order]=0 THEN [State] END

Label : Other States

IF [State Order] = 1 THEN ‘All Other States’ END

Both these fields are added to the Tooltip side by side and coloured accordingly. As only one will ever be true per row, only one will display :

Count of States for chart title

To count the states based on which group they were in, required a couple of table calcs :

Count States Listed

WINDOW_SUM(IF ATTR([State Order]) = 0 THEN COUNTD([State]) END)

Count States Grouped

WINDOW_SUM(IF ATTR([State Order]) = 1 THEN COUNTD([State]) END)

These were added to the Detail shelf, so could then be referenced in the title of the Viz. The table calc needed to be applied per State Grouping

Then it was just a case of hiding various pills, and formatting to remove row lines and gridlines. A reference line for the selected parameter was also added.

My published version is here. The main difference to Ann’s solution is that she used Sets to group the States together.

Happy vizzin’

Donna

High Level Dashboard with Explain Data

Donna ColesLeave a comment

One of the main reasons I like to partake in the weekly #WorkoutWednesday challenges, is to get the chance to try out the newer features of Tableau that I can’t use at work, due to the version we’re using (we’re never in a position to react to upgrades at the same speed as Tableau releases).

This week, Lorna provided a challenge to introduce Explain Data which is a feature Tableau released as part of v2019.3, so to complete this challenge, you’ll need this version of Tableau Desktop.

The challenge itself isn’t that taxing, so I’m going to attempt not to make this blog that lengthy (which is a challenge in itself… 🙂 ).

Having not used Explain Data before, then the first thing I did was google it, and I watched & read the info Tableau had published here. There’s absolutely nothing wrong in using online resources to help you complete these challenges :-).

So with that understood, I built the basic vizzes required:

Sales by Quarter

Straightforward line graph….

This Year vs Last Year

A few calculated fields needed for this one:

Latest Year

{FIXED : MAX(YEAR([Order Date]))}

Previous Year

[Latest Year]-1

Sales This Year

IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END

Sales Last Year

IF YEAR([Order Date]) = [Previous Year] THEN [Sales] END

The [Sales This Year] is then plotted against [Sub-Category] with [Sales Last Year] added to the Detail shelf and displayed as a reference line against each cell.

The [Sub-Category] field is hidden, so the row labels aren’t duplicated when displayed next to each other on the dashboard.

Sales by Order ID

This is a dot plot that has then been ‘jittered’ to spread the marks out vertically in a random manner.

Without the jitter, you’d just get…

…where all the circle marks are arranged on the same horizontal line, and subsequently, the majority all appear on top of each other.

To make the jitter effect, I make use of a hidden function in Tableau called Random() which assigns a random no between 0 and 1 to each mark on the row.

Jitter

Random()

Then adding Jitter to the rows (and disaggregating), a new axis gets added and the marks get spread vertically, giving a much better sense of the volume of marks that are around the same values.

Hide the axis to get the desired display. The vertical dotted line is a reference line set to be the average sales for each row.

For further reading on creating jitter plots using Random() or Index() please check out :

So with the 3 charts built, these can be added side by side on a dashboard, and with all set to Fit Entire View, you can guarantee each row will line up. You can adjust the padding of each object to make it look like the row divider lines actually continue as a single line (I forgot to do this on my published version :-)).

Explain Data

So having read the information on Explain Data, I switched to my dot plot, selected a mark, and used the ‘lightbulb’ in the command toolbar to view the Explain Data information. I then saved each of the suggested explanation visuals as it’s own sheet, which I added side by side on another dashboard.

The text at the bottom of each worksheet, is simply the caption text that is automatically generated, and can be exposed on the dashboard by enabling the caption to show.

And that’s it for this week. A relatively straight-forward challenge, but providing some good learning opportunities. My published viz is here.

Happy vizzin!

Donna

Can you build a dynamic scatter plot with proportional brushing?

Donna ColesLeave a comment

It’s #data19 in Vegas, so this week’s #WorkoutWednesday challenge, set by Curtis was run ‘live’ at the Conference. Not being there, I had to wait patiently at home until it was released and build solo 😦

Given it was a live challenge, Curtis wanted to ensure it offered the right balance for those newer to Tableau & #WorkoutWednesday, while still giving the more experienced participants something to get their teeth into. And I think he managed this well.

Building the scatter plot

The scatter plot is built to be dynamic, with both the axes and displayed marks changing based on user selection. This is driven by 3 parameters

Set Level of Detail

String parameter, containing 3 values : Product, Manufacturer, Customer

(quick side note here…. the challenge states to use the Superstore Sales dataset that comes with v2019.1. To get the Manufacturer field, you need to use the provided Tableau data source and not the excel file – this always trips me up!).

Set X-Axis

String parameter containing 4 values : Quantity, Sales, Orders, Discount

(another note … the challenge says to use Profit, but the solution provided used Orders – I chose to use Orders too so I could verify a match)

Set Y-Axis

The same as above (just duplicate the parameter and rename).

So with the parameters in place, we need to build calculated fields to reference them.

LOD

CASE [Set Level of Detail]
WHEN ‘Customer’ THEN [Customer Name]
WHEN ‘Manufacturer’ THEN [Manufacturer]
WHEN ‘Product’ THEN [Product Name]
END

X-Axis

CASE [Set X-Axis]
WHEN ‘Quantity’ THEN SUM([Quantity])
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Orders’ THEN COUNTD([Order ID])
WHEN ‘Discount’ THEN SUM([Discount])
END

Y-Axis

CASE [Set Y-Axis]
WHEN ‘Quantity’ THEN SUM([Quantity])
WHEN ‘Sales’ THEN SUM([Sales])
WHEN ‘Orders’ THEN COUNTD([Order ID])
WHEN ‘Discount’ THEN SUM([Discount])
END

(basically duplicate X-Axis and edit & rename)

With these we can now build the basic scatter : [X-Axis] on Columns, [Y-Axis] on Rows and [LOD] on Detail :

Changing the parameters and the scatter will change too.

So now its just a case of formatting and adding a few additions to match the display.

First up, the solution has the X-Axis scale on the top, rather than the bottom. This is achieved by dual-axis.

Duplicate the [X-Axis] on the Columns shelf (Press Ctrl, click on the [X-Axis] pill and drag next to it.

Make dual-axis and synchronise

Edit the bottom axis, deleting the title of the axis, and changing the Tick Marks to None.

The solution also shows each axis is labelled based on the parameter selection. To do this, edit both the left and top axis to remove the titles, then add the [Set X-Axis] parameter to Columns and [Set Y-Axis] parameter to Rows.

Now right-click on the displayed Set X-Axis and Set Y-Axis and ‘hide field labels for columns/rows’, and right-click on Discount and ‘rotate label’.

Finally, adjust the tooltip to contain :

<Parameters.Set Level of Detail> : <LOD>

That’s the core viz built, it just needs some formatting applied, to

  • remove column and row lines
  • remove gridlines (the 0-lines are retained)
  • Change mark type to circle, adjust colour to suit and add border

Bar Charts & Proportional Brushing

When it’s all together on the dashboard, the selection of marks in the scatter should cause the bar charts to update to show the % of each variable accounted for in the selection. This concept is referred to as proportional brushing, and makes use of the Set Actions feature. Tableau’s own KB article describing Set Actions and proportional brushing can be seen here.

The first thing we need to do is build a set : right click on the [LOD] field and select Create -> Set. In the Set dialog, name the set Selected LOD and randomly select some values (not all)

We also need various calculated fields :

# LOD

COUNTD([LOD])

Just counts the number of distinct items in the user selected LOD. When bringing the Selected LOD set into play, we can the count the number of items in or out of the set, and using a quick table calculation, change to get the % of total. I tend to start using data tables at this point, to sense check the data I need before I viz :

I’ve turned on Grand Totals for columns too, just so I can check the numbers are what I expect.

Adding the [X-Axis] and [Y-Axis] values into the table, and finding % of total too

and we’ve got all the % needed to make up the bars. At this point, edit each table calculation and fix it to compute by the In/Out Selected LOD (so if you shift pills around later, you won’t change the values).

While we could build the viz at this point, I noticed that the tooltip on the bar chart displays the same information whether you hover the ‘in’ or the ‘out’ portion of the bar

With the data we currently have, we couldn’t do this, so we need a few more fields/calculations. We need to be able to get the total value of the LOD/measure against each row, as well as the number and % of the ‘In’ items.

We’ll use table calculations for this.

Total #LOD

WINDOW_SUM([# LOD])

# LOD Selected

ZN(WINDOW_MAX(IF ATTR([Selected LOD]) THEN COUNTD([LOD]) END))

This counts the number of items selected in the set, and the WINDOW_MAX statement will then ‘spread’ that value across both the rows (as only 1 row contains a value).

% LOD Selected

[# LOD Selected]/[Total # LOD]

This is formatted to 1 dp.

Adding into the ‘check’ table, you can see how the values with table calculations are matching the initial set

A similar set of steps are the repeated to get the X & Y axis values

So now I’m happy I’ve got all the fields needed to build the bars, we can, and this is done in 3 separate sheets (it can be done on a single sheet, but I noticed some spacing on Curtis’ solution that suggested it wasn’t).

LOD Bar

You can build from scratch, or as I tend to do, duplicate the check sheets, remove the pills I don’t want, and shuffle the rest around. You ultimately want :

  • [# LOD] as % of total on Columns
  • [Selected LOD] set on Colour (adjust colours, and make sure the Out is listed before In on the colour legend, so the darker values show first)
  • [Total LOD], [# LOD Selected] , [% LOD Selected] on Tooltip

The format to remove axis title, show axis ticks etc.

Name the sheet, then duplicate to create an X-Axis version, dragging the equivalent pills over the top, and do the same for the Y-Axis version. If you do this, just double check all the values are matching your check sheet, and the table calc settings haven’t been lost.

Putting it all together on the dashboard

Build the dashboard, and add the 4 sheets (the scatter and 3 bars) into their appropriate place, and expose the 3 parameters too.

To get the proportional brushing technique to apply to the bars, add a Set Action to change the value of the Selected LOD set when marks are selected in the scatter plot. The Set Action needs to be set to ‘remove all values from set’ when the marks are unselected.

And that’s it! My published version is here. Enjoy!

Happy vizzin’

Donna

What is our daily fulfilment rate by region?

Donna ColesLeave a comment

This week Luke challenged us to create Donut Charts in Tableau, but added additional requirements to make it just a bit spicier. The full details are here.

Taking inspiration from Ann’s challenge the week before, Luke provided an advanced version to incorporate grey circles for each date when there were no orders, but stated this should be achieved without any data duplication or densification. Deep down, I knew Luke really meant ‘only one sheet’, but he didn’t state this, so my initial interpretation was to tackle the challenge in a similar way to Ann’s, and use 2 sheets; a background with all the grey circles displayed, and a foreground with the donuts/green circles, and float one over the other. This week, while still tricky, I managed to get them aligned, so that was the version I published and released.

I documented this floating technique last week, here, so have no plans to repeat, except to state that I got all the dates to display by creating a Baseline Date field of

MAKEDATE(2019, MONTH([Order Date]), DAY([Order Date]))

which is basically setting all the dates in the dataset to the same year. I then used this date to build my background viz, and as there was an order in every region for the day/month combination, I didn’t get any gaps. If the dataset didn’t exhibit this behaviour, this technique would not work, so it isn’t the most robust solution.

Right, now I’ve finished talking about that, let’s get onto building the chart…

Fixing the report date

Luke stated the report should be based on the last 7 days from 6th Nov 2019, so this date had to essentially be ‘hardcoded’ into the report. The easiest way to do this in a way that could be quickly changed if need be, was to create a parameter, Report Date, defaulted to 6th Nov 2019.

Restricting to 7 days

I created a calculated field to limit the data just to the days I was interested in

Dates to Include

[Order Date] >= DATEADD(‘day’, -7, [Report Date]) AND [Order Date]<= [Report Date]

Determining the % of orders shipped

This required a few calculated fields. First up, we needed to know if the order had shipped before the report date or not

Has Shipped?

[Ship Date]<= [Report Date]

# Shipped

IIF([Has Shipped?],1,0)

This returns 1 or 0 depending on whether the order has shipped before the report date or not. This field when added to the viz, can then be aggregated (summed) to provide a count of the orders that have shipped.

% Shipped

SUM([# Shipped])/SUM([Number of Records])

How many shipped as a proportion of all records. NOTE – in this solution, an order equates to a row in the data set. However technically, an order can contain multiple rows, so when counting orders, you could think you need to COUNTD([OrderID]). This is certainly valid, and IMO more accurate, but things do get a bit more complex this way. Luke had counted rows, so I decided to work with getting the data to match his solution.

Finally we needed to know if the orders had been fully shipped or not, as this is what will determine whether we’re showing a green circle with a tick, or a donut.

Fully Shipped?

[% Shipped]=1

Building the Donuts

I’ve worked with donut charts before, so knew that the trick was in using a dual axis technique to show a pie chart with a smaller circle mark on top.

I also needed ‘axis’ to work with, so incorporated our old friend MIN(0), and added 2 instances as synchronised dual axis like below:

The first instance, I changed to be of mark type Pie chart, adding SUM([Number of Records]) to the Angle shelf, and Has Shipped? to the Colour shelf. The colours were set to green if Has Shipped? was true and grey otherwise. The colours were reordered, so True is listed first, so the green starts at 12 o’clock.

On the second instance, I changed the mark to be a circle, adjusted the size so it was smaller, and added Fully Shipped? to the Colour shelf. This time the colour is set to green if true, but white if false.

For the labels, I created 2 calculated fields :

Label Tick

IF [Fully Shipped?] THEN ‘✓’ END

Label % Shipped

IF NOT([Fully Shipped?]) THEN [% Shipped] END

and added these side by side to the Label shelf of the Circle mark. The font was adjusted to fit, and centre aligned.

Removing the column/row labels, hiding some of the pills and formatting the axis/gridlines etc, and the core of the viz is built. This would achieve the ‘standard’ solution.

Date Format

The order date was set to a custom format of mmm d, ‘yy to get the desired display.

Displaying the grey circles

As stated above, I originally used the 2 sheets & floating technique to show the circles within the gaps.

After publishing, I had a look at Luke’s solution to see how he’d tackled it.

However much I stared at his workbook though, I could not see what he’d done. All I could tell by playing around was that there were no further marks than what I had already got displayed.

Then Rosario Guana posted up her solution, so I checked hers out too…. still couldn’t see it. I went through every menu option I could think of, trying to find what the secret was…..

Eventually I pinged Rosario a message… background image was the response.

Doh! I don’t use these often, well actually I’ve only used it once on another #WorkoutWednesday challenge that Rody Zakovich set sometime ago. Tableau’s help on using Background Images (which you often associate with maps, and hence is under the Maps menu option), is here.

So now I had the clue, what did I need to do….

First up, I needed an image to use. As I’d already downloaded Rosario’s workbook, I used the one in her workbook.

Secondly, I needed ‘proper’ pills to map the co-ordinates of my image to, my ‘typed in’ MIN(0) wouldn’t suffice. So I created

Col

0.0

Row

0.0

I then rebuilt the viz with Col (set to be a dimension), and added Row to the rows shelf (again set to be a dimension). I hid all the headers again

I then added the image via Map > Background Image > <Data source name>, then Add Image, and applied settings below

The sizes just then needed to be adjusted to fit, but this is best done when displayed on the dashboard itself, as what you see in the sheet view might not present the same when on the dashboard.

Both my versions of this challenge can be viewed here.

Happy vizzin’

Donna

Can you build a Sales calendar with top 3 highlighting?

Donna Coles1 Comment

An interesting challenge set by Ann this week, which seemed initially to me that it should be quite straightforward, but as usual ended up causing all sorts of frustrations.

The trickiest part was how to show marks for days when there weren’t any sales. From my initial observations of Ann’s viz (hovering over marks etc), I’d figured out that she had made use of the ability to set a sheet’s background as transparent and then float one sheet over the other. This was because when I hovered over the circles with no days displayed, the mark didn’t ‘highlight’ in any way, indicating that it was ‘behind’ something else.

So with this thought in mind, I went about trying to build the basic viz – a calendar view with all days for the background, and a calendar view with just the days with sales to layer over the top.

Ann didn’t state any requirements about how this could be tackled, so I assumed she hadn’t used any sort of date scaffolding (ie using another data set containing all the dates). I therefore tried to do the same, and did end up with something, but it just felt too complex, and wasn’t really ‘future’ proof for a real world scenario, as it relied on each day across a year existing in the dataset being used, regardless of year (ie there was an entry for 1st Jan, 2nd Jan etc all the way through to 31st Dec, but it could be 1st Jan 2016, 2nd Jan 2018 etc). Knowing I had to blog about what I’d done, I decided to take a step back, and approach the challenge based on what I would have done if I was asked to do something similar for work.

I still wanted to incorporate the transparency/floating sheets requirement though, since this was a piece of functionality I believe Ann was trying to demonstrate the use of in this challenge.

Date Scaffold

I first needed to create a data source to use as my date scaffold. This is simply an excel sheet containing 1 column, Date, with 1 row per day ranging from 01 Jan 2017 through to 31 Dec 2019.

I created this, saved the sheet and then connected Tableau Desktop to it. This will provide the data for my background calendar.

Building the Background Calendar

The overall calendar needs to be organised into 3 columns (based on months) and 4 rows (based on quarters). Within each column, there is then a column per day of week and within each row there is a row per week of the year. Essentially we’re building a trellis type chart.

Column Number

When building trellis charts, there’s all sorts of clever techniques to flex the number of rows & columns based on how many ‘entities’ in the data you’re trying to organise. In this instance we’re dealing with months and we know the data is going to be static, so I just created a simple calculated field to determine which column each month should sit in :

CASE MONTH([Date]) WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 1
WHEN 5 THEN 2
WHEN 6 THEN 3
WHEN 7 THEN 1
WHEN 8 THEN 2
WHEN 9 THEN 3
WHEN 10 THEN 1
WHEN 11 THEN 2
WHEN 12 THEN 3
END

This field was then added to the columns shelf, and next to it I added WEEKDAY([Date]) .

Top Tip – by right clicking on a Date field in your dimensions pane, and dragging onto the columns/rows shelf, you get a date selection dialog displayed when you release the mouse, so you can quickly choose the type of date you want to display

By default, as I’m based in the UK, my weeks are set to start on Mondays, but the display starts on a Sunday. To fix this, right click on the datasource and select Date Properties

Onto the rows, I added QUARTER([Date]) using same technique as above.

Now I need to get the weeks to display, but if I just add WEEK([Date]) to rows, I get too many rows for each quarter

What I want is to be able to show weeks 6-9 and 10-14 on the same rows as weeks 1-5. To achieve this I created

Week Index

DATEPART(‘week’, [Date]) – {FIXED DATEPART(‘month’,[Date]): MIN(DATEPART(‘week’, [Date]))}

The FIXED part of the calculation is finding the minimum week number for each month. So for January, the minimum week number is 1, for February it’s 5, for March it’s 9. That minimum number is then being subtracted from each week number, so for

  • week 1 (in Jan), I have 1-1 = 0
  • week 2 (in Jan), I have 2-1 = 1 etc
  • week 5 (in Feb) I have 5-5 = 0
  • week b (in Feb) I have 6-5 = 1 etc

Adding Week Index to Rows rather than WEEK([Date]) gives me

which is the layout I’m after.

Restrict the Year

I created a parameter SELECT YEAR which I listed 2017, 2018, 2019. I then created a new calculated field

FILTER : Year

YEAR([Date]) = [SELECT YEAR]

which I added to the Filter shelf and set to True

I then just had to apply some formatting :

  • Set the mark type to circle and choose a light grey colour, increase the size slightly
  • Set the row banding
  • Lighten the row/column divider lines
  • Set the Q1, Q2 etc label headings to white font (this is the background sheet, so I don’t want them to display at all)
  • Set the Sunday, Monday etc label headings to white font
  • Don’t show the Week Index header
  • Don’t show the Column Number header
  • Hide all other column/row labels
  • Name the sheet Backgound

Building the Foreground Calendar

Using the Superstore dataset, I basically repeated all the steps above, but this time referencing the Order Date field in the data set so

Column Number

CASE MONTH([Order Date]) WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 1
WHEN 5 THEN 2
WHEN 6 THEN 3
WHEN 7 THEN 1
WHEN 8 THEN 2
WHEN 9 THEN 3
WHEN 10 THEN 1
WHEN 11 THEN 2
WHEN 12 THEN 3
END

Week Index

DATEPART(‘week’, [Order Date]) – {FIXED DATEPART(‘month’,[Order Date]): MIN(DATEPART(‘week’, [Order Date]))}

FILTER: Year

YEAR([Order Date]) = [SELECT YEAR]

which I again added to the Filter shelf and set to True

When plotted on the sheet in the same way, you can start to see the gaps appearing where there is no sales for that day.

I then applied the following changes

  • Set mark type to circle, colour slightly darker grey than that selected above, added border to circle, and adjusted size slightly to match the other sheet
  • Removed the row banding completely
  • Removed the row/column divider lines
  • Set the Q1, Q2 etc label headings to larger font aligned middle & centre
  • Set the Sunday, Monday etc label headings to darker font, and formatted to just show First Letter
  • Don’t show the Week Index header
  • Don’t show the Column Number header
  • Hide all other column/row labels
  • Labelled the sheet Foreground
  • Added Order Date to the Text shelf, setting it to be Discrete Exact Date, then changed the format to dd/mm. Changed font size to 7 and centre aligned.
  • Added Sales to the tooltip, setting the format to be $ with no decimal places
  • Created a copy of Order Date (right click > duplicate), an added the copy to the tooltip, setting the format to be Wednesday, 14 March 2001
  • Adjusted the tooltip to match
  • Changed the background of the whole sheet from white to ‘None’ – this sets it to be transparent, and is the main trick for this display

Highlight Top 3

To achieve this requirement, I first created a parameter HIGHLIGHT TOP 3 containing the values DAYS, WEEKS, MONTHS

When MONTHS was selected, I needed to find the top 3 months in the year, etc. So I created some calculated fields

Total Monthly Sales

{FIXED YEAR([Order Date]), MONTH([Order Date]): SUM(Sales)}

This stores the total month’s sales against every row

Total Weekly Sales

{FIXED YEAR([Order Date]), WEEK([Order Date]): SUM(Sales)}

This stores the total week’s sales against every row

Value

IF [HIGHLIGHT TOP 3] = ‘MONTHS’ THEN SUM([Total Monthly Sales])
ELSEIF [HIGHLIGHT TOP 3] = ‘WEEKS’ THEN SUM([Total Weekly Sales])
ELSE SUM([Sales])
END

This is basically storing the relevant value I need to consider for the Top 3 based on what was selected in the parameter.

I then also created an additional field

Group By Date

IF [HIGHLIGHT TOP 3] = ‘MONTHS’ THEN DATETRUNC(‘month’,[Order Date])
ELSEIF [HIGHLIGHT TOP 3] = ‘WEEKS’ THEN DATETRUNC(‘week’, [Order Date])
ELSE [Order Date]
END

This captures a date at the relevant level on each row depending on the parameter selection.

In Top 3

From the Group By Date field, I then created a set, which I set to be Top 3 by Value

To see how all these fields interact, build a basic viz with Group By Date on rows (exact date, discrete), and Value on Text. Add In top 3 to rows too. Then add FILTER- Year to the filters shelf, and add to context. This step is crucial to ensure the year filter is applied before the set computes its top 3. Use the parameters to see how the values of Group By Date and Value change

So now you can see how the set is working with the parameter, the set can now be added to the Colour shelf of the Foreground sheet, and the colours adjusted accordingly. The FILTER – Year needs to be added to context on this sheet too.

Building the dashboard

So now the two sheets have been created, the dashboard can be built.

I started by setting the size of the dashboard to 1600 x 1300, adding a text field for the title, and text fields underneath for my standard ‘footer’. I then added the Background sheet into the middle between my title & footer, moving the parameters to form part of the title row. I hid the title of the sheet and set to Fit -> Entire View.

At this point everything on the dashboard is tiled.

I then changed the option to floating, and added my Foreground sheet. As with the background, I hid the title and set to Fit -> Entire View.

I then used the position values of the Background sheet (the x & y position and the height & width), to set the position values of the Foreground sheet to be exactly the same. The intention here was the circles on the Foreground should then be positioned directly over the circles on the Background, and as the Foreground was transparent, the circles that were missing on the Foreground where there were no sales, would show through from the Background sheet, along with the row banding & the row/column lines.

However, this just wouldn’t work as I hoped. I checked the padding options, I shifted things slightly left, right, up & down but when I got some circles lining up, others wouldn’t. It really was quite frustrating and I spent some time trying to fix this, but ended up publishing as it was, which you can see here. In truth I was secretly hoping that by publishing to Tableau Public, it would miraculously work, but it didn’t :-(.

I checked out Ann’s viz and she had managed to get it all to line up beautifully, although the position values on her sheets weren’t exact either, so I’m not sure if this was just trial & error to get right too. I’ll have to watch her solution when it’s published.

So that was attempt 1, but I really wasn’t happy, so came up with an alternative…

Take Two – Join the data at source

For this version, I used Tableau’s ability to join two excel data sources together to create a single data source, with 1 row for every day from 01 Jan 2017 to 31 Dec 2019, supplemented by the relevant superstore sales data against each date if there was a sale on that date.

The data was left outer joined, using Date = Order Date as the joining key

I then created the viz on a single sheet using Dual Axis. To get an axis though, I had to create a Week Index (Date) field and a Week Index (Order Date) field (just as the two Week Index fields described above), but when added to the view, they were set to continuous with the axis reversed to ensure the 0 was at the top rather than the bottom.

All the data required to colour the circles, apply the tooltips etc was added to the 2nd Week Index (Order Date) marks card.

The only difference with this version, is that when you hover on the non-labelled circles, they do ‘highlight’. I can live with this, and felt the solution was much ‘cleaner’ and far less complex 🙂 However it does rely on the fact that the types of data sources I was working with could be joined. In my day job, a lot of the data sources I use are published Tableau Server data sources, and as yet Tableau doesn’t allow these to be joined 😦

This version is published here .

Happy vizzin’

Donna

Show me all the casinos within a certain distance from another

Donna ColesLeave a comment

It’s getting near #data19, Tableau’s annual customer conference, which once again is being held in Las Vegas. So for #WorkoutWednesday this week, Lorna decided to incorporate this into her challenge, which was also intended to showcase some of the new features introduced into v2019.3 (so you’ll need this version at least to complete the challenge).

For the challenge, Lorna provided a dataset in a .hyper format that simply consisted of the (casino) name, address and it’s associated latitude and longitude values.

The core requirement was to show all the casinos within a user defied radius from another casino, which was selected by user by clicking on the map.

So we need to

  • identify the selected casino
  • determine the lat and long of the selected casino (the start)
  • determine the distance (in miles) between the selected casino and all the other casinos
  • identify the radius
  • restrict the display to just those within the specified radius

Identify the selected casino

This is simply a parameter Selected Casino which is initially hardcoded to have the default of “Mandalay Bay Resort & Casino” (it’s important you get this spelt right using the exact case too).

I then have a boolean calculated field

Is Selected Casino?

name = [Selected Casino]

which is true if the name of the casino in the dataset matches the value stored in the parameter

Lat & Long of selected casino

My aim is to get the latitude and longitude of the selected casino to be stored against each row in the dataset, so I need to use an LoD to do this

Selected Casino Lat

{FIXED : MIN(IIF([Is Selected Casino?], [latitude],NULL))}

Selected Casino Long

{FIXED : MIN(IIF([Is Selected Casino?], [longitude],NULL))}

Determine the distance (in miles) between each casino and the selected one

Here we can make use of the new 2019.3 function DISTANCE (defined here) within a new calculated field :

Distance (miles)

DISTANCE(MAKEPOINT([Selected Casino Lat],[Selected Casino Long]), MAKEPOINT([latitude],[longitude]), ‘miles’)

The first MAKEPOINT function above determines the start point using the information about the selected casino, and the second MAKEPOINT determines the end point, using the casino location information stored on each row.

Identify the radius

The viz requires the data to be restricted just to those within a defined radius, so we need another parameter, How many miles? to capture this value.

In Lorna’s viz, the parameter has a fixed range, which is specified as part of the parameter properties


In order to then restrict the casinos shown to just those within range, we need

Within specified miles?

[Distance (miles)]<= [How many Miles?]

The final thing we also need at this point, is a Distance (metres) field as this is used to colour the marks on the viz, and is also referenced on the tooltip.

Building the map

So now we’re at the point we can start building the map.

My initial observations upon hovering over Lorna’s published solution, was this would need to be a dual axis map. The text on the tooltip differed dependent on whether the mark was the selected one or not. The colour of the marks also differed; for the selected mark, the colour is black, while for the non selected marks the colour varied based on the distance.

So with this in mind, I decided to build another calculated field

Is Selected Lat

IF [Is Selected Casino?] THEN [latitude] END

which would just store a latitude value against the row of the selected casino only, as demonstrated below (I set it to have a geographic role of latitude)


I figured I’d then plot this as a dual axis on the map, but as soon as I added, second latitude pill, my display changed from having a map background, to a standard scatter plot chart 😦

So I started to go down the route of just making it a single axis chart, but the tooltips started to get too complicated, and the colouring just wasn’t working….

So I reverted back to the original plan, but this time, used the same latitude pill for my dual axis, which retained the map background

I now needed to differentiate on each map between the selected casino, and the others, and did this initially by Size

Size (selected)

IF [Is Selected Casino?] THEN 2 ELSE 1 END

Size (non selected)

IF [Is Selected Casino?] THEN 2 ELSE 1 END

These are independent fields which can be adjusted differently, and one is placed on the Size shelf of each marks card and adjusted so on one chart the selected mark is the biggest, and all the other marks are as small as they can be. On the other chart, the selected mark is made as small as possible.

I then started adding the various other features

  • added Distance (metres) to the colour shelf on the non- selected marks card, changing the colour palette to purple, and reversing the range so the lower the distance, the darker the colour
  • changed the mark type to Shape on the selected marks card and added Is Selected Casino? to the shape shelf, changing the shapes accordingly
    • The location marker image I found on flaticon.com (remember to add an attribution on your viz if need be to the icon creator)
    • See this blog for how to get the shape into Tableau
  • Set the tooltip appropriately for each marks card
  • Filtered the view to restrict to the casinos in range

The map background needs to be changed to street view, which is set via the Map -> Map Layers setting in the menu

We can now make the map properly dual axis, and can put on a dashboard.

Changing the selected casino

Now it’s on the dashboard, we can use a parameter action to change the value of the selected casino ‘on click’

Displaying count of casinos within radius

The dashboard shows a summary on selection, which I added as the title of the map sheet

I created a calculated field

Count Casinos

TOTAL(SUM([Number of Records])) – 1

which needs to exclude the selected casino, hence the subtraction. I added this to the Detail shelf, so I could reference it in the sheet title.

Ensuring all the casinos remain ‘visible’ on selection

By default, on selection of a different casino, all the other casinos within range are faded out, which we don’t want to happen

So I used the same ‘dummy’ trick with highlight action that I documented in a previous blog to prevent this from happening


The dashboard just needed a few more tweaks – adding the title (which references the parameters), adding the colour legend and the distance parameter, and I was done. I published my viz.

Except I wasn’t completely happy…

When I increased the number of miles, the location shape became ‘hidden’ beneath the other marks, but if I changed the order of the axis, tiny marks were visible on each casino, which I couldn’t get the colour to match

This stumped me, so I had to check out Lorna’s solution, but I stared for some time and just couldn’t figure out what she’d done. It was Rosario Gauna’s solution where I immediately saw what I had to do, as Rosario had made a comment in the worksheet caption as a reminder.

Frustratingly, this is exactly what I do to remind me of this sneaky trick… the small marks on the ‘selected casino’ mark shelf, were ‘hidden’ by simply right clicking on the Is Selected Casino? shape legend and ‘hiding’ the false values

Doh! I wish there was a way you could see more easily on the sheet in Desktop where someone has done this.

My published viz is here.

Happy vizzin!

Donna