Search Results for: trellis

Let’s build a trellis chart!

Donna ColesLeave a comment

It only seems like yesterday I was writing a solution guide, and I’m back at it again. This week Sean asked us to recreate this challenge to build a small multiple / trellis chart using table calculations only.

A note on the data

After downloading and connecting to the provided data source, I found the dates weren’t coming through as intended – they’d been transposed from dd/mm/yyyy to a mm/dd/yyyy so consequently the only dates I was getting were for the first 12 days in January for every year. Rather than trying to solve this at source, I just created a new field which transposed the Date field back so it behaved as I expected

Date Corrected

(MAKEDATE(YEAR([Date]), DAY([Date]),MONTH([Date])))

You may not need to do this if the data pulls in correctly.

Filters

There are two filters that should be applied, which can either be added as data source filters (right click data source > Edit data source filters) or can be applied to the Filter shelf on any sheets created. Ultimately, this challenge only requires 1 sheet, but when building and verifying logic, I tend to have additional ‘check sheets’. I therefore added the filters below to the Filter shelf of the first sheet I started working with, but set them to apply to all worksheets using the data source (right click pill once it’s on the filter shelf -> Apply to Worksheets -> All using this data source).

Gender : All

Date Corrected : starting date = 01 Jan 2012

Setting up the data

As is good practice when working with table calculations, I start by building out the calculations I need and validating them in a tabular format before I build any vizzes. So let’s do that.

All the countries are displayed in capital letters, so we need

Country UPPER

UPPER([Country Name])

Add this to Rows

Additionally, for the purpose of validation and performance only, add this field to the Filter shelf too and just filter to Australia and Austria.

If you haven’t already added them as data source filters, apply the filters mentioned in the section above to this sheet too and set to apply to all worksheets using the data source.

Add Date Corrected to Rows as a discrete (blue pill) exact date. Format the date so it displays in month year format.

Add Unemployment Rate to Text. Format this number to 1 decimal place and add a % as a suffix.

Now for the table calcs

Median

WINDOW_MEDIAN(SUM([Unemployment Rate]))

Format this to display as a % using the same option as above. Add this to the table and set to compute using Date Corrected

You should find that your median value only differs by country.

Now we work out

Variance

SUM([Unemployment Rate]) – [Median]

Format this to display as a % and add to the table, setting the table calc to compute by Date Corrected again. This is the measure that will be used to plot the trend line against.

We also need to display the range of Unemployment rates for each country – ie we need to work out the minimum and maximum values.

Max Unemployment Rate

WINDOW_MAX(MAX([Unemployment Rate]))

Min Unemployment Rate

WINDOW_MIN(MIN([Unemployment Rate]))

Format both of these to display as 5 with 1dp, and add to the table, verifying the calculations are computing by Date Corrected once again. Verify you get the same values for all the rows associated to a single country.

Now we know the calculations are as expected, we can start to build out the viz.

Building the core chart

To start with we’ll just focus on getting the line chart with the associated text displayed for the two countries Australia & Austria. So on a new sheet add Country (UPPER) to Filter and filter to these selections. The other filters should automatically add.

Add Country UPPER and Date Corrected (green continuous exact date) to Columns and Variance to Rows. Set Variance to Compute By Date Corrected.

Add Unemployment Rate to the Tooltip and adjust the text to match.

To add the country title and other displayed text, we’re going to use a ‘fake axis’ and plot a mark at a central date. On hovering over the solution, October 2016 seems to be the appropriate date selected. So we need

Title Position to Plot

IF [Date Corrected] = #2016-10-01# THEN 1 END

Add this to Rows in front of the existing pill. Change the mark type of this measure only to a circle and re-Size to make it as small as possible and adjust the Colour Opacity to 0%. This will make the mark ‘disappear’.

Add Country UPPER, Median, Max Unemployment Rate and Min Unemployment Rate to the Label shelf of this marks card. Ensure all the table calculation fields are set to compute by Date Corrected. Adjust the text as required, and align centre. Ensure the Tooltip is blank for this marks card.

Change the colour of the variance line to grey, then remove all gridlines, row dividers and axis. Set the Column Dividers to be a thick white line (this will help provide a separator between the small multiples later).

Creating the trellis

There are multiple blog posts about creating trellis charts. My go to post has always been this one by Chris Love. It’s a more complex solution that dynamically flexes the number of rows and columns based on the number of members in the dimension you’re visualising. There have also been other Workout Wednesday challenges involving trellis charts, which I’ve blogged about too (see here).

Ultimately we’re aiming to determine a ‘grid position’ for each member of our dimension. In this case the dimension is Country UPPER and its a static list of 36 values, which we can display in a 6 x 6 grid. So Australia needs to be in row 1 column 1, Austria in row 1 column 2….. Costa Rica in row 2 column 1… USA in row 6 column.

As our members are static, the calculations we can use for this can be a bit simpler than those in Chris’ blog.

Firstly, let’s get our data in a tabular layout so we can ‘see’ the values as we go.

Duplicate the data sheet we built up, then move Measure Name and Date Corrected from Rows/Columns to the Detail shelf. Remove the Country UPPER field from the Filter shelf. You should have something like below, showing 1 row per country

Double click into the Rows shelf and type in INDEX(), then change the resulting pill to discrete (blue). You will see that index numbers every row. It’s a table calculation and although working as expected, let’s explicitly set it to compute using County UPPER.

Let’s now create our grid position values.

Cols

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

This takes the Index value and subtracts 1, and returns the remainder when divided by 6 (%6=modulus of 6 – ie 6%6=0, 7%6=1). 6 is the number of columns we want.

Rows

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

This takes the Index value and subtracts 1, and returns the integer part of the value when divided by 6. Again we’re using 6 as this is the number of rows we want to display.

Add these to the table, set to be discrete (blue) and compute using Country UPPER.

You can see that the first 6 countries are all in the same row (row 0) but different columns (0-5).

Now that’s understood, we can create the small multiples on the viz.

Duplicate the sheet we created further above which displays the trend graph for Australia & Austria. As we’re now going to make the changes to create the charts for every country, if things go a bit screwy, you can always get back to this one to try again :-).

Add Cols to Columns. Set to discrete and compute using Country UPPER. Add Rows to Rows and do the same thing. Move Country UPPER from Columns to the Detail shelf on the All marks card. Then remove Country UPPER from the Filter shelf.

Hopefully everything worked as expected and you have

Final step is to uncheck Show Header against the Cols and Rows pills so they don’t display and you can add to a dashboard.

My published viz is here.

Happy vizzin’!

Donna

Advertisement

Can you label & sort small multiples?

Donna ColesLeave a comment

A colourful #WOW2022 challenge this week set by Kyle Yetter and using his favourite data – Baseball. Let’s jump straight in.

Building the required calculations

First up we need to calculate the core measure the viz is based on – % of wins

Win %

SUM([Wins])/SUM([Games])

I formatted this to 3 decimal places, then applied a custom number format to remove the leading 0 (custom number format looks like ,##.000;-#,##.000).

We also need to know the number of losses as this is part of the tooltip.

Losses

SUM([Games]) – SUM([Wins])

Let’s pop all these out into a table (I formatted all the whole numbers to display without any decimal places).

The viz however isn’t plotting the actual Win%, it’s plotting the difference from 50% (or 0.5), so values less than 50% are negative and those above are positive.

Plot Postion

[Win %] – 0.5

And we also need to know whether the Win% is above 50% or not

Above 50%

[Win %]>0.5

Pop these out onto the table too

The viz also displays the overall Win% for each team, and also uses this to sort the data. As it is used for sorting, we need to use an LoD calculation (rather than a table calculation).

Overall Win% LOD

{FIXED [Team]:SUM([Wins])} / {FIXED [Team]: SUM([Games])}

for each team, get the total wins, and divide by the total games for the team. Format this to 3 dp with no leading 0 as before.

pop this into the view (you’ll see it’s the same value for each row for a single team), and then apply a Sort on the Team field to sort descending by the Overeall Win% LOD.

Now we have the data sorted, we can create the fields needed to build the trellis chart.

I have already blogged challenges relating to trellis charts / small multiples (see here) which in turn reference other blogs in the community, so I’m not going to go into all the details. We just need to build two calculated fields to identify which row and which column each Team will sit in. The table is fixed at 6 columns wide as the data wea re using is static. Some solutions work with a more dynamic layout depending on how many entities you need to display. We’re keeping things simpler.

Cols

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

Rows

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

Add both these fields to the table as discrete dimensions (blue pills), and as they are both table calculations, set them both to Compute Using – > Team.

Building the Core Viz

On a new sheet, add Cols to Columns as discrete dimension, Rows to Rows as discrete dimension and Team to Detail. Set both Rows and Cols to Compute Using Team.

Add Year as continuous (green) pill to Columns and Plot Position to Rows and change the mark type to Bar and reduce the size. Sort the Team field based on Overall Win% LOD descending.

Add Wins, Losses, and Win% to the Tooltip shelf and adjust the tooltip to display as required. Add Above 50% to the Colour shelf (you may need to readjust the size). Leave the colours as they are for now – we’ll deal with this later.

Adding the labels

Create a new calculated field

Dummy Plot

FLOAT(IF [Year]=2000 OR Year = 2020 THEN 0.35 END)

This is basically going to position a mark at height 0.35 but only if the year is either 2000 or 2020. These values were all just based on a bit of trial and error as to what worked to get the desired result.

Also create a field

LABEL:Team

IF [Year]=2000 THEN [Team] END

and

LABEL:Win%

IF [Year]=2020 THEN [Overall Win % LOD] END

format this to 3dp and exclude the leading 0.

Add Dummy Plot onto Rows and change the mark type of this measure to circle. Amend the Tooltip of this marks card so it’s empty.

Add LABEL:TEAM and LABEL:Win% to the Label shelf, and adjust the label so both fields sit side by side (only 1 value will only ever actually display). Adjust the table calculation of both the Rows and Cols pills so they now compute using both the Team and the LABEL:Team fields.

Adjust the alignment of the labels so they are positioned bottom centre. Set the font colour to match mark colour and bold.

Then reduce the size of the circle mark to as small as possible, reduce the opacity of the mark colour to 0.

Now make the chart dual axis and synchronise the axis. Remove the Measure Names field that has automatically been added to the All marks card.

Hide all the headers and axis (uncheck Show Header), remove all grid lines, zero line, axis rulers.

Hide the null indicator (bottom right).

Colouring by Team

Copy the colour palette text Kyle provided into your preferences.tps file (usually located in the My Tableau Repository directory). For more information on working with custom colour palettes see this Tableau help article.

You’ll need to save your workbook and re-open for the new palette to be available for use.

In order to prevent having to manually set all the colours (and believe me you don’t want to do this!), perform the following steps in order

  • Add Team to also be on the Colour shelf. Click on the 3 dots (…) that are to the left of the Team pill on the All marks card, and change it to Colour. This means there are now 2 fields on colour. Move the Team field so it is listed above the Above 50% pill. This means your colour legend should be listed as <Team>, <True|False>
  • Adjust the Sort of the Above 50% pill, so it is manually sorted to list True before False.

  • Now change the Sort on the Team field so it is sorted alphabetically ascending instead. This will cause the viz to change its sort order, but don’t worry for now. It also changes the list on the colour legend, so ARI, True is listed first then ARI, False etc.

  • Now edit the Colour Legend and select the new MLB Team Colours palette we added. Click the Assign Palette button to automatically assign the colours. As we’ve made sure the entries listed are in the right order, they should get the correct colours.

  • Change the Sort on the Team field back to be based on Overall Win% LOD descending

And that should be it. You can now add the viz to a dashboard and publish. My published version is here.

Happy vizzin’!

Donna

Can you swap States?

Donna Coles1 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 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

Drill Up and Down with Parameter Actions

Donna Coles1 Comment

The final guest #WorkoutWednesday challenge for August was set by zen master, ambassador and fellow #WorkoutWednesday enthusiast Rosairo Gauna.

The challenge is here and built on a challenge set earlier in the year by Luke Stanke which involved Set Actions. Rosario was challenging us to use Parameter Actions instead to navigate the hierarchy (they weren’t around when Luke set his challenge), as well as not duplicate the data set.

I have to say, this really stumped me. Huge kudos to Rosario for managing to figure out how to use Parameter Actions in this way.

I had to reference Rosario’s own blog post to get to the bottom of this, so I won’t be documenting this myself.

This is going to be one of those challenges that demonstrates a technique that if I ever want to use again, I’ll be referencing this post (well Rosario’s post via this post), rather than remembering all the steps/calculations… a bit like I do when I have a need to build a trellis chart or if I ever had to build a radial chart again – I have my ‘go to’ reminder posts.

My solution is here.

Happy vizzin!

Donna