Can you visualise headcount distribution in multiple ways on a single sheet?

Donna Coles5 Comments

This week’s challenge focuses on allowing the user flexibility in both how they want to ‘slice’ the measure being reported (actual headcount), and how they want the results displayed to meet the needs of the question they are trying to answer. Whilst the above could be built using multiple sheets and ‘sheet swapping’ using dynamic zone visibility, the aim is to try to build within a single sheet.

Let’s get cracking.

Enhancing the data

Not all the fields we need to slice the data by are included initially in the data provided, so we need to define some additional calculated fields, as stated in the requirements.

Contract Type

IF [FTE] = 1 THEN ‘Full Time’ ELSE ‘Part Time’ END

To determine the Age Bracket an employee belongs to, we first need to know their age. Usually this would be based on today’s date (using the TODAY() function), but as this data is static, we’re going to pretend today is 01 Dec 2022, by capturing this in a parameter.

pToday

date parameter defaulted to 01 Dec 2022

We can then work out how old the employee is (in complete years) on 01 Dec 2022

Employee Age

IF DATEPART(‘dayofyear’, [Birth Date])<=DATEPART(‘dayofyear’,[pToday]) THEN
DATEDIFF(‘year’, [Birth Date], [pToday])
ELSE
DATEDIFF(‘year’, [Birth Date], [pToday])-1
END

The ‘dayofyear’ parameter of the DATEPART function returns as it suggests the day of the year, so is a number from 1 to 365 (or 366 in a leap year). So this calculation is basically saying, if the birthday falls before ‘Today’, then they’ve had their birthday in that year, so a simple difference between the years suffices, otherwise the birthday hasn’t happened yet, so take one off the difference in years.

We can then create

Age Bracket

IF [Employee Age] < 20 THEN ‘Under 20’
ELSEIF [Employee Age] < 30 THEN ’20-29′
ELSEIF [Employee Age] < 40 THEN ’30-39′
ELSEIF [Employee Age] < 50 THEN ’40-49′
ELSEIF [Employee Age] < 60 THEN ’50-59′
ELSE ‘Over 60’
END

Setting up the parameters

All the user interactivity is driven through selection of parameters.

pDisplayType

string parameter defaulted to Butterfly Chart with a list of values Stacked, Side by Side, Butterfly which each have a slightly different Display As value (up to you whether you want to do this).

pDisplayRowsBy

string parameter defaulted to Age Bracket with required options listed.

pSplitBarsBar

string parameter defaulted to Contract Type with required options listed

Building the Viz

The pDisplayRowsBy parameter defines the field that we want to display on the Rows shelf. The parameter is just a string value though which we need to map to a proper field from the data set

Dimension – Rows

CASE [pDisplayRowsBy]
WHEN ‘Age Bracket’ THEN [Age Bracket]
WHEN ‘Contract Type’ THEN [Contract Type]
WHEN ‘Department’ THEN [Department]
WHEN ‘Gender’ THEN [Gender]
WHEN ‘Nationality’ THEN [Nationality]
END

The pSplitBarsBy parameter defines the field that will be used to colour the bars. Again we need to map this to fields in the data set

Dimension – Colour

CASE [pSplitBarsBy]
WHEN ‘Gender’ THEN [Gender]
WHEN ‘Contract Type’ THEN [Contract Type]
END

The existing Employees(Count) field that is automatically included is just a counter for the number of rows in the data set, so is essentially our headcount number. However, we need to think about how the display needs to look for the different types of chart, specially the butterfly chart.

When building a butterfly chart, the bars to the right of the middle axis are plotted as positive numbers, while the bars to the left are plotted as negative numbers (so the middle is actually 0). So in the event the display type of Butterfly Chart is selected, we need to make some adjustments, so that some of the values are plotted on the negative axis. This means our calculation looks like

Headcount

IF [pDisplayType] = ‘Butterfly’ THEN
IF MIN([Dimension – Colour]) IN (‘Part Time’, ‘Female’) THEN COUNT([Employees]) * -1
ELSE COUNT([Employees])
END
ELSE
COUNT([Employees])
END

If it’s a Butterfly chart, then if the values associated to the field we’re colouring the bars by are ‘Part Time’ (pSplitBarsBy = Contract Type) or ‘Female’ (pSplitBarsBy = Gender), then negate the count of employees, otherwise just return the count of employees.

Let’s start putting some of this into a viz to see what we’ve got…

On a sheet, add Dimension-Rows to Rows and Headcount to Columns. Add Dimension-Colour to Colour. Show the 3 core parameters and adjust the colours to suit.

Change the pSplitBarsBy to Gender, and adjust colours again. Change to ‘N/A’ and adjust again, so all possible options for the Dimension-Colour field have been set. Change pSplitBarsBy back to Contract Type.

Now change pDisplayType to Stacked Bar.

We want Full Time segment of the bar to appear first against the zero line of the axis, so manually re-order the options in the colour legend, so Part Time is listed first.

Change pSplitBarsBy to Gender and verify that the Male segment of the bars are displaying first against the zero line of the axis. Change pSplitBarsBy back to Contract Type.

For the side by side bar chart, we need another dimension on Rows that is essentially the Dimension -Colour field. However, we only want the value when the pDisplayType is Side by Side. So we need

Dimension – Side by Side

IF [pDisplayType]=’Side By Side’ THEN [Dimension – Colour] ELSE ” END

Add this to Rows after the Dimension-Rows field.

This adds an additional column to the display. While we’re in side by side or butterfly ‘mode’ , nothing is displayed. Change pDisplayType to Side by Side and we get the separation required.

Since the Colour legend is defining what the bar relates to, we can hide this field (right click on the Dimension – Side by Side pill and uncheck show header.

We want to also show the % of Total headcount split for each row. Create a new field

Total Employees Per Row

{FIXED [Dimension – Rows] : COUNT([Employees])}

then create

% Total Headcount

[Headcount]/SUM([Total Employees Per Row])

and format to a custom number format of 0%;0%. This is a % with 0 decimal places, but the custom format is used as we want negative numbers (caused by the butterfly chart type) to be displayed as +ve percentages.

Add % Total Headcount to Columns and verify the display for the different display types. Hint – if the calc is working as expected, the Stack Bar option should show 100% 🙂

Labelling the Viz

We can’t simply add labels using the usual method (show mark labels) and adjusting the positioning, as it won’t always show what we want, where we want for all the different display types. So we need to employ different techniques based on the chart being displayed.

Let’s start with the stacked bar chart. For the Headcount bars, we want to display the total number of employees at the end of the bar, and for the % Total Headcount, we want the % displayed in the middle of the bar.

Create a new field

Ref Line – Stacked

IF [pDisplayType] = ‘Stacked’ THEN [Headcount] END

Add this to Detail shelf of the Headcount marks card, then add a reference line to the Headcount axis (right click axis > Add Reference Line) that for each pane references the Sum of the Ref Line – Stacked field, and just displays the Value on the Label. No line or tooltip is displayed.

Format the reference line (right click the ‘invisible’ line) to adjust the colour and alignment of the font.

Add % Total Headcount to the Label shelf of the % Total Headcount marks card. You may need to adjust the width of the bars so you can see the values. Adjust the font to be white.

Change the pDisplayType parameter to Side by Side. No labels should be displayed. This is because the label we used for the headcount bar was based on the display type, and the label we used for the % total has been coloured white and is therefore invisible on a white background (set it to black and you’ll see it displayed). So again we need more fields

Ref Line – Side By Side

IF [pDisplayType] = ‘Side By Side’ THEN [Headcount] END

Add this to the Detail shelf of the Headcount marks card, and again add a reference line to the Headcount axis, this time setting the scope at the cell level.

Once again format the reference line to set the font and alignment.

Create

Label – % of Total Side by Side

IF [pDisplayType] = ‘Side By Side’ THEN [% Total Headcount] END

Format to a % with 0 dp and add this to the Label shelf of the % Total Headcount marks card, and then adjust the Label so this field is appropriately coloured and listed before the other label (which is coloured white).

Change the pDisplayType parameter to Butterfly chart. The % labels should already display. We just need to sort the labelling for the actual headcount. For this we need

Ref Line – Butterfly Pos

IF [pDisplayType] = ‘Butterfly’ THEN
IF [Headcount] >= 0 THEN [Headcount] END
END

and

Ref Line – Butterfly Neg

IF [pDisplayType] = ‘Butterfly’ THEN
IF [Headcount] < 0 THEN [Headcount] END
END

Add both these fields to the Detail shelf of the Headcount marks card. Then add reference lines for each of the two fields

Then format both the reference lines so the font and alignment is set as required (in the case of the Ref Line – Butterfly Neg reference line, it needs to be left aligned)

Add Headcount and % Total Headcount to the Tooltip shelf of the All marks card, and then adjust the tooltip. Finally tidy up by

  • Adjusting format of the Dimension – Rows values and aligning middle left.
  • Hide field labels for rows
  • Remove column dividers
  • Adjust row dividers to be more subtle
  • Remove gridlines, zero lines, axis ticks & rulers
  • Remove the Headcount & % Total Headcount axis.

Add the viz to a dashboard. I used a horizontal container placed above the viz to add text objects to represent the headings. The first text box just the referenced the pDisplayRowsBy parameter value, so it was dynamic.

My published viz is here.

Happy vizzin’!

Donna

Can you manually overwrite a value in a table?

Donna ColesLeave a comment

For this week’s challenge, Erica wanted us to be able to set a discount value for a Sub-Category which once set, overwrote the value displayed in the table and applied the discount to the other visuals. She added an additional complexity to display the input field aligned with the selected Sub-Category. She alluded to the fact this last requirement was likely to be tricky, and she wasn’t wrong. I managed to build a solution, and I’ll walk through the principles, but there will be a bit of trial and error involved….

Building the table

We will need to capture the discount value to apply in a parameter, so create

pDiscount

integer parameter defaulted to 5

and we also need capture the Sub-Category to apply the discount to

pSubCat

string parameter defaulted to Art

The discount to display will need to be adjusted for the selected Sub-Category

Discount to Display

IF [Sub-Category] = [pSubCat] THEN [pDiscount]/100 ELSE [Discount] END

format this to % with 1 dp.

Add Category and Sub-Category to Rows. Double-click into rows and manually type in MIN(1). Change the mark type to shape and change the shape to be a transparent shape (see this blog for more details). Add Discount to Display to the Label shelf, and change the aggregation to Average. Align middle centre. You should see that the value associated to Art is 5%.

Note – you may be wondering why this is not being displayed in a standard table – why the need for the fake axis? I can’t recall exactly why I ended up with this, but it will have been borne out of later steps, and the need to try and get the input parameter aligned – by all means feel free to try without and see how it goes 🙂

Hide the MIN(1) axis, remove column dividers and gridlines / zero lines, axis rulers etc. Add subtotals (Analysis menu > Totals > Add all subtotals). Stop the Tooltip from displaying. Adjust the height and width of the cells so you can see all the rows on the screen. Show the parameters, and test the functionality by manually changing the parameters.

Create a new field

Index

INDEX()

Set this to be a discrete field and add to Rows after Sub-Category. Adjust the table calculation so it is set to compute using both Category and Sub-Category, and you show see the rows numbered from 1 to 17 (except for the total rows).

In order to help us position the input parameter, we will need to capture the index of the selected Sub-Category, so create a parameter

pIndex

integer parameter defaulted to 6 (the value associated to Art)

For now, we’ll leave the index displaying in the table. But we will hide it eventually. Name this sheet Table.

Building the line chart

The line chart needs to show the actual Sales and the sales as they would be if the revised discount was applied for the selected Sub-Category. The value stored in the Sales field will already account for the original value stored in the existing Discount field. So to work out what the adjusted Sales will be, we need to determine the full sale price (Sales / (1 – Discount)) and then apply the inputted discount value from pDiscount (multiply by (1- (pDiscount/100))

Adjusted Sales

IF [Sub-Category] = [pSubCat] THEN ([Sales] / (1-[Discount])) * (1- ([pDiscount]/100))
ELSE [Sales] END

format this and the Sales fields to $ with 0do

On a new sheet add Order Date at the continuous Month/Year level (green pill) to Columns. Add Sales to Rows, and then drag Adjusted Sales and drop it on the Sales axis when the green ‘2 column’ icon appears. This will automatically add Measure Values to Rows and Measure Names to Filter and Colour.

Calculate the difference as

Difference

IF [pSubCat]<>” THEN
(SUM([Adjusted Sales]) -SUM([Sales])) / SUM([Sales])
END

and apply a custom number format of ▲0.00%;▼0.00%;0%

Format the date axis to display dates as custom format mmm yy, and edit the axis to change the title to Month.

Add Sales, Adjusted Sales and Difference to the Tooltip and update to suit. Show the pDiscount parameter and update it to a really large value, say 10,000. The 2 lines will show more prominently and the Sales axis will adjust its scale.

The requirement is to ensure the grey line (the original sales) doesn’t move, so edit the value axis, adjust the title to Sales and then fix the start from 0, but end ‘automatic’

This will push the Adjusted Sales off the chart. Reset the pDiscount to something more reasonable like 5.

Remove row/column dividers and gridlines, but retain axis rulers. Name the sheet Line.

Building the KPI card

On a new sheet, add Sales to Text. Change the Mark type to shape and select a transparent shape. Align the text middle centre, and set the display to Entire View.

We want to only show the Adjusted Sales if a Sub-Category has been selected, but we need to line chart to display a blue line all the time, so we need another field

Label Sales

IF [pSubCat]<>” THEN [Adjusted Sales] END

format this to $ with 0dp and add to the Label shelf.

Adjust the layout and display of the text as required. Hide the Tooltip. Name the sheet KPI.

Right, we’ve got the key components. Let’s get these all on a dashboard first.

Building the dashboard

Getting all the objects you want on the dashboard (including titles, footers etc) positioned exactly where you want them, with the appropriate padding set is crucial to getting the method I’m going to use to reposition the input parameter. It’s also quite fiddly and I can’t guarantee that even if you follow the steps, you’ll get things looking right…

Anyway, let’s start with the dashboard.

I set the dashboard size to 1100 x 650, then I added a floating vertical container which I positioned 0,0 and sized 1100 x 650. I formatted the dashboard and set the background colour to light grey.

I then switched to Tiled and added a text box for my title. I set the background of this to white, outer padding to 0 and inner padding to 5.

I then added another text box beneath for the instructions. I set the outer padding to 0 and inner padding to 5. I then fixed the height to 70.

Next I added a horizontal container beneath the instructions. I add a blank object to it as a placeholder. Ensuring the horizontal container was selected (blue border), I set the outer padding to 5.

I then added another horizontal container beneath this, and added my standard footer (created by, recreated by etc). I set the outer padding of this container to have 5px on the left and right, and 0 on top and bottom. All the text boxes within I set to have 0 outer padding and 0 inner padding.

I then added another text box beneath to add in the link to the challenge, also part of my ‘standard footer. I set the outer padding for this text box to 0.

I then calculated how high all the ‘rows’ of objects were on the dashboard (the height of the title + the height of the instructions + height of my standard footer and challenge link), and then subtracted this from 650. I then fixed the height of the central horizontal container based on this value

Add the Table sheet into the left side of this container. Remove the title. Set the background to white. Adjust the outer padding to 0. Set the sheet to Fit Width. Very carefully, adjust the width of a row, so the table fills as much of the vertical space as possible without there being a scroll bar. This is really fiddly to do.

The addition of the table will have automatically added some parameters and a Tiled object to the layout. We’ll deal with these shortly, but leave them be for now.

Add a Vertical container to the right hand side. Add the KPI sheet and then then Line sheet underneath. Remove the blank object that was the placeholder. Widen the vertical container so the vizzes have more space. For both the KPI and the Line objects, remove the title, set the background to white, set the outer padding to 0. Set the inner padding of the KPI chart to 20, and the inner padding of the line chart to 10. Adjust the height of the KPI chart so its visible.

If all is well, you should have something like

Adding the interactivity

Create a parameter action

Set Sub Cat

On select of the Table chart, set the pSubCat parameter, passing in the value from the Sub-Category field. Reset to ” when unselected.

Set Index

On select of the Table chart, set the pIndex parameter, passing in the value from the Index field aggregated to None. Reset to 0 when unselected.

If you click different Sub-Categorys, the KPI and line chart will change. Once unselected, no adjusted sales or discount will display.

Getting the parameter to move

Duplicate the Table sheet, and remove all subtotals. On this sheet, we’re only going to display the rows up to the row before the selected Sub–Category. For this we need

Show Top Rows

[pIndex]=0 OR [Index]<[pIndex]

Add this to the Filter shelf and set to True. Verify the table calculation is computing by both Category and Sub-Category. If need be adjust, then recheck the filter is just displaying True still. Show the pIndex parameter and just test the filter is working, by changing the value. Here, with the parameter set to 6, it is showing rows up to 5.

What we’re trying to do is just display the rows necessary so that the parameter input can be added beneath this sheet on the dashboard. The reason we have removed the subtotals, is that if the index is set to 2, we only want to display 1 row above; that for Bookcases. With subtotals included we’d get a row for Bookcases and a Total row. However once we get to a new Category as we have above, we need to show an additional row to accommodate for the subtotal displayed within the original table.

So we need to force additional rows to show in some circumstances but not others.

To help with this I have made use of the Region field. I checked that for two regions, Central & East, there were Sales in both Regions for every Sub-Category.

Add Region to the Filter shelf and set to Central & East only. Add Region to the Detail shelf. Remove Discount to Display from the Label shelf. Create

Extra Row

IF LAST()=0 THEN STR(ATTR([Region]) )
ELSE ''
END

Add this to Rows after Index. Adjust the table calculation so that it is computing by Sub-Category only. With pIndex set to 0, this should just display the two Regions against the last Sub-Categorys in each Category, simulating the subtotal rows.

But set the index to 6 and we just get the additional rows for the Furniture Category

and set to 2 and we just the row for Bookcases

Hide the Category column (uncheck show header). Name the sheet Top. Set the pIndex back to 0, and let’s start seeing how this works on the dashboard.

Add a vertical container between the existing table and the kpi/line chart. Reduce the width. Add the Top sheet into this container. Remove the title and set the inner and outer padding to 0. Set the sheet to fit width. If you’re lucky, all the rows should align. If not you may need to tweak again.

Select a Sub-Category in the original table, and the 2nd table should shrink.

From the Tiled section on the layout item hierarchy, navigate through until you find the pDiscount parameter. Click it to select it on the dashboard, then move that object to sit beneath the shortened table. Then select the Tiled section on the item hierarchy, and right click and remove from dashboard, which will remove all the unnecessary parameters/legends that were being displayed.

For the pDiscount object, remove the title, set the background to white and set the outer padding to 0. Set the background of the vertical container to white too. We only want this discount parameter to show when a Sub-Category has been selected. To manage this, we need

Show pDiscount

[pSubCat]<>”

Select the pDiscount object on the dashboard, and then from the Layout tab, check the Control visibility using value and choose the Show pDiscount field

Unselecting the Sub-Category and the field won’t display

So now we’ve got the basics of what we’re trying to do, we obviously don’t actually want any of the table to be visible. But if we hide all the fields (uncheck show header), we lose the column headings which was helping with the positioning, as we can see below – the input box is no longer aligned with Art.

To fix this, create a new field

Dummy Header

”

and add to the Columns of the Top sheet. If you haven’t already, uncheck show header against all the other blue pills (Category, Sub-Category, Index and Extra Row). The sheet should look like below, and what the Dummy Header has done is create an extra spacing at the bottom of the page, which compensates for the heading we don’t have at the top… and this is the reason for creating a table using a fake axis 🙂

Back on the dashboard, everything is aligned again.

… except when we select Bookcases … argghhh!

Add a blank object above the parameter. Set the padding to 0, and adjust the height to about 18 px – enough to bring the parameter in line. Create a new field

Show Blank

[pIndex]=1

and use this to control the visibility of the blank object – ie we only want the blank object to come into play when Bookcases is selected, and nothing else.

Click around every Sub-Category and hopefully the parameter box is aligned each time.

Final touches

On the Top sheet, remove row dividers, so the sheet just always looks empty.

On the Table sheet, hide the Index field (uncheck show header).

Add left outer padding of 10px to the vertical container that contains the Top sheet and the pDiscount parameter. This should mean some grey spacing appears between the table and the input field.

Adjust the width of the objects to suit BUT DON’T fiddle with the heights at all!

To stop the other discounts from ‘fading’ when a Sub-Category is clicked, create a new field

HL

‘HL’

and add to the Detail shelf on the Table sheet. Then on the dashboard, add a highlight dashboard action that on select of the Table sheet, targets the Table sheet with the HL field only. This essentially has the effect of highlighting all the fields, since the HL field is applicable to every row.

Phew! This took some time and a lot of fiddling to get right, and even then I know there’s every chance that you can’t quite get things to align just right, or publishing to Tableau Public and it all seems to shift … My published viz is here. Fingers crossed you’re successful!

Happy vizzin’!

Donna

Can you make a page navigator?

Donna ColesLeave a comment

Yoshi set this week’s challenge to build a page navigator, but there was so much more in it too, so this could be a bit lengthy 🙂

Note, I’m blogging based on the full ‘advanced’ challenge, to include an ‘apply all’ button as well. I built the following sheets to build this via and I’ll talk through the basics of each of them in turn

  • List of State names
  • Bar chart of total State sales
  • Line chart of monthly State sales
  • Jitter plot of State sales by order
  • Navigation page number buttons
  • Back arrow
  • Forward arrow
  • Filter summary
  • Apply button

Preparing the data

The data being presented is only applicable to the states of the US. In the latest versions of Superstore, information for both Canada and the US is included, so I started by adding a data source filter to include only Country/Region = United States (right click data source -> Add Data Source Filter).

Building the list of State names

Add State/Province to Rows, and apply a sort to sort by the field Sales descending

Add State/Province to Text and to Colour. Adjust font to be bold and widen each row.

Create a new field

Index

INDEX()

and add to Rows before State/Province. Set the table calculation to be explicitly computing by State/Province. Index is essentially ranking each State from 1 to 49, as we’ve already sorted the listing of the states.

The requirement is to show up to 7 states on a page, so create

Page No

INT(((INDEX()-1) /7)) +1

Set to be a discrete field and add to Rows in front of Index. Again explicitly set the table calculation to compute by State/Province. This shows us which states are on which page.

We’re going to identify the page we’re on based on a parameter

pPageSelected

integer parameter defaulted to 1

Show the parameter, then create a new field

Is Selected Page

[pPageSelected] = [Page No]

Add to the Filter shelf. Initially select All. Then adjust/verify the table calculation is explicitly set to compute using State/Province. Then edit the filter to just show values that are True.

Adjust the pPageSelected parameter to test the functionality.

Hide the Page No, Index, and State/Province field from Rows (uncheck show header). Remove column dividers and don’t show the tooltip. Name this sheet States.

Building the bar chart

Note – to get the labelling and the spacing between the bars, this isn’t a ‘standard bar chart’. This is a technique that has been included in previous WOW challenges.

On a new sheet, add State/Province to Rows and sort by Sales descending. Add Sales to Columns and Add State/Province to Colour. Add a grey border to the bars (via Colour shelf).

Double click into Rows and manually type MIN(1.0) and change the Mark Type to bar. Add Sales to Size, then click on the Size button and adjust the size from Manual to Fixed and align right.

Add Sales to Label and align top left. Adjust the Tooltip. Add Index to the front of Rows and adjust the table calculation to be computing by State/Province.

Add Page No to the front of Rows and adjust to computing by State/Province.

Set the page to Fit Width. Show the pPageSelected parameter and add Is Selected Page to the Filter shelf and set to True. Verify the table calculation is set to compute using State/Province. If not adjust, and then recheck the filter is just showing True value.

Change the parameter to show page 2. You’ll notice the axis has now adjusted from 0 – $80,000 whereas on page 1 it went up to $450,000. We want to retain the axis scale across the pages. For this, create

Max Sales

WINDOW_MAX(SUM([Sales]))

Add this to the Detail shelf and ensure the table calculation is computing by State/Province.

Add a reference line to the bottom Sales axis (right click axis > add reference line) and set it to cover the entire table, using the average Max Sales value. Don’t show any label., tooltip or line

The axis will now have readjusted and display up to 450,000 regardless of the page you’re on.

Adjust the Min(1.0) axis to be fixed from -0.5 to 2 to add some white space around the bars.

Hide both axis, the Page No, Index and State/Province fields in Rows. Remove all gridlines, zero lines, axis rulers & tick marks. Remove column dividers. Add Row Banding.

Name the sheet Total Sales.

Building the line chart

On a new sheet, add State/Province to Rows and sort by Sales descending. Add Index and Page No to Rows too and adjust both table calcs to be explicitly computing by State/Province.

Add Order Date to Columns and adjust to be at the continuous Month/Year level (green pill ). You’ll notice the page numbering & indexes start to look odd – ie multiple states have same index.

Adjust the Order Date field to Show Missing Values, and our numbers are all aligned again.

If we just add Sales to Rows though, the indexes all mess up again due to the there being no values for some points.

To fix this create

Sales to Plot

ZN(LOOKUP(SUM([Sales]),0))

This returns 0 if there is no value for the date / state combination. Add this to Rows instead and adjust the table calculation so it is computing by both State/Province and Month Order Date.

Edit the Sales to Plot axis, so it is displays an independent axis range for each row or column – this makes the records near the bottom show peaks, rather than just a straight line.

Add Is Selected Page to the Filter shelf and set to True. Verify the table calculation is set to compute using State/Province. If not adjust, and then recheck the filter is just showing True value.

Add State/Province to Colour. Adjust Tooltip. Reduce the Size of the line.

Hide both axis, the Page No, Index and State/Province fields in Rows. Remove all gridlines, zero lines, axis rulers & tick marks. Remove column dividers. Add Row Banding.

Building the Jitter Plot

On a new sheet, add State/Province to Rows and sort by Sales descending. Add Index and Page No to Rows too and adjust both table calcs to be explicitly computing by State/Province. Add Sales to Columns and Order ID to Detail. Change mark type to circle.

Readjust the table calc settings of Page No& Index to also include Order ID, but also set the leval at State/Province.

Add State/Province to Colour, and reduce the opacity.

To get the marks to not overlap so much, create a new field

Jitter

RANDOM()

add add to Rows as a dimension. Again adjust the table calcs so Jitter is also included in the settings.

Add Max Sales to Detail and adjust the table calc settings to be computing over all 3 fields – State/Province, Jitter & Order ID.

Add a Reference line to the Sales axis across the entire table, using the average of Max Sales and don’t display any label/tooltip or line.

Add Is Selected Page to the Filter shelf and set to True. Verify the table calculation is set to compute using all fields and at the level of State/Province. If not adjust, and then recheck the filter is just showing True value.

Adjust the Tooltip. Hide both axis, the Page No, Index and State/Province fields in Rows. Remove all gridlines, zero lines, axis rulers & tick marks. Remove column dividers. Add Row Banding.

Name the sheet Sales by Order.

Building the Navigation Number Buttons

On a new sheet add State/Province to Rows and sort by Sales descending. Add Index to Rows and set the table calc to compute by State/Province. Move Index to Columns and State/Province to Detail.

Change the mark type to square and add Index to Label, aligning middle centre.

Create a new field

Colour – Page No

[Index] = [pPageSelected]

and add to the Colour shelf. Verify table calc is set to compute by State/Province. Adjust colours to suit and add a dark border.

We only want to show the indexes relating to the number of pages we have, which in turn is going to be based on what the data has been filtered by. So firstly we want to understand what the maximum number of pages is

Max Pages

IF SIZE()%7 = 0 THEN INT(SIZE()/7) ELSE INT(SIZE()/7)+1 END

If the number of results (ie number of states after filtering has occurred – the SIZE()) is exactly divisible by 7 (%7 = 0) then divide the results by 7 to get the max number of pages, otherwise, increment this value by 1. Eg if 14 results, it’ll be 2 pages, but 15 results will require 3 pages.

Now we know that, we can create

Pages to Show

INDEX() <= [Max Pages]

Add this to the Filter shelf. Set the True, Then adjust the table calc settings to be explicitly computing by State/Province for all nested calcs too.

Re-edit the filter to ensure it just shows True results.

Hide the Index from Rows and don’t show row/column dividers. Don’t show the tooltip. Name the sheet Page Nos.

Building the back arrow

On a new sheet, show the pPageSelected parameter, and change the mark type to Shape.

Create a new field

Show Page Back

[pPageSelected]>1

Add to the Shape shelf. If pPageSelected = 1, then False should display and adjust the shape to use a transparent shape (refer to this blog on how to set this up). Change the pPageSelected parameter to 2 and adjust the shape of the True option to be a filled arrow. Change colour to black.

On the dashboard ,we will need to define what page is being navigated to on click, so we need

Page Back

IF [pPageSelected]>1 THEN [pPageSelected]-1 END

Add this to the Detail shelf as a dimension.

Name the sheet Page Back.

Building the Forward Arrow

This is slightly more tricky than the back arrow, as we need to know how many pages are being displayed to know when we no longer need to show the arrow.

On a new sheet, add State/Province to Detail and sort by Sales descending. Remove the Lat/Long fields that automatically get added and change the mark type to shape. Create a new field

Show Page Forward

[pPageSelected]<[Max Pages]

and add to the Shape shelf. Set the table calc to be computing by State/Province explicitly. Set the mark type for ‘True’ to be a filled arrow and adjust colour to black.

Show the pPageSelected parameter and set to 7. Adjust the ‘False’ option to be a transparent shape.

Once again, on the dashboard, we will need to define what page is being navigated to on click, so we need

Page Forward

IF [pPageSelected]<[Max Pages] THEN [pPageSelected]+1 END

Add this to the Detail shelf as a dimension, and verify table calc is set to compute by State/Province explicitly.

We only want 1 arrow to show at most, so add Index to filter. Set to 1, then adjust table calc so it is set to compute by State/Province explicitly, and then re-edit filter to just select 1 again. Name the sheet Page Forward

Building the Filter Summary

On a new sheet add Category, Segment and Ship Mode to the Detail shelf and change the mark type to polygon..

Edit the Title of sheet and update as required

Name the sheet Filter Summary

Building the Apply Button

The basic outline for this is documented in this Tableau KB article here.

Create a calculated field

Apply

‘Apply Filters’

and add to Rows on a new sheet.

Add Category, Segment and Ship Mode to the Detail shelf and to the Filter shelf (set to All for each). Change the mark type to polygon. Right click the work ‘Apply’ in the column header and select hide field labels for rows.

Right click on the words ‘Apply Filters’ and select Format – set the shading of the header to teal.

As well as applying filters when the button is clicked, the page needs to reset to the first page. For this create

Reset Page 1

1

Add this to the Detail shelf as a dimension.

Adjust the size and colour of the font. Remove row dividers. Set the background of the worksheet to light grey. Remove the Tooltip. Name the sheet Apply Button.

Creating the dashboard

Now we have all the components, we can arrange the objects on a dashboard.

I added the 4 sheets making up the main viz int a horizontal container. All the sheets had the titles hidden, were set to fit entire view and had 0 padding, which gives the illusion of them all being a single viz. I added some outer padding to the container itself.

I used another horizontal container positioned above this one to add text boxes to give the viz headings.

Another horizontal container was placed above the title one. IN the left hand side I placed the Filter Summary viz., and in the right, I added a vertical container.

The vertical container had a blank and then a horizontal container underneath the blank object. The horizontal container then stored the page back, the page nos and the page forward sheets.

Another horizontal container was place above all this and I add the Apply Button sheet. I then moved the 3 filter objects automatically added to the sheet into this horizontal container too. I set the background of this container to light grey

Adding the interactivity

Multiple dashboard actions are needed to get the page to function as required. Now, I did have issues getting somethings to behave as I wanted, and I believe it was something to do with the order in which the actions were added. I can’t prove this… all I know is that I spent a long time trying to figure out why the filters I selected were getting reset when I pressed a page number, but removing all actions and adding again worked…

You need these actions

Apply Filters

Filter action that on select of the Apply Button sheet, targets all other sheets. Clearing the selection keeps filtered values. Category, Segment and Ship Mode should be passed through as selected fields.

Set Page No from Square

Parameter action that on select of the Page Nos sheet, sets the pPageSelected parameter passing in the value from the Index field that is not aggregated. Clearing the selection, keeps the current value.

Reset to Page 1

Parameter action that on select of the Page Nos sheet, sets the pPageSelected parameter passing in the value from the Reset Page 1 field that is not aggregated. Clearing the selection, keeps the current value.

Prev Page From Arrow

Parameter action that on select of the Page Back sheet, sets the pPageSelected parameter passing in the value from the Page Back field that is not aggregated. Clearing the selection, keeps the current value.

Next Page From Arrow

Parameter action that on select of the Page Forward sheet, sets the pPageSelected parameter passing in the value from the Page Forward field that is not aggregated. Clearing the selection, keeps the current value.

With these actions, you should be able to test the functionality, but you will find some fields become greyed out/ need clicking twice. We need to automatically ‘deselect’ them on click. For this I applied the basic principles discussed here.

Create new calculated fields

True

TRUE

False

FALSE

Add both these fields to the Detail shelf of the Page Back, Page Forward, Page Nos, and Apply Button sheets. Then add a dashboard filter action for each sheet.

Deselect Apply

On select of the Apply Button sheet on the dashboard, target the Apply Button sheet itself (ie not the object on the dashboard), passing the selected fields of True = False. Show all values when the selection is cleared.

Repeat the above for the Page Back, Page Forward and Page Nos sheets.

Hopefully with all this you have a fully functioning dashboard. My published viz is here.

Happy vizzin’!

Donna

Can you rebuild this Tube Map?

Donna ColesLeave a comment

Lorna called on the help of a data schooler, Olivier Newman to set this week’s challenge, which will be part one of a 2-part challenge.

For those of you who are regular readers of my blog, you’ll know that working with maps and spatial data isn’t something I do often, so challenges like this always start with me feeling a little bit daunted by what’s required.

Side Note – I originally built this challenge using Tableau Desktop v2024.1, but encountered some issues with getting the data on the map updated as I made changes to the selections – the selection changes were visible on other tabular sheets, just not on the map, unless I forcibly refreshed the data source. Recreating in Tableau Desktop v2023.3 was fine. And the version published from v2024.1 to Tableau Public also worked fine on Tableau Public. I have raised this to Tableau via Slack channels I have access to, so if you experience similar issues, that may be why…

Understanding the data and the requirement

I initially spent some time trying to understand how the data matched up to the information I could see on the viz, specifically what was being listed in the Arrival Station selection box.

I found, every Station was associated with a Line, but the Station could be associated to more than one Line. Every Line was associated to a Branch, but again, the Line could be associated with more that one Branch. Picking some specific Stations as an example…

  • Amersham Station is associated to 1 Line (Metropolitan) which is associated to 1 Branch (Metropolitan Line Branch 0) – so Amersham is associated to 1 Branch
  • Bank Station is asscociated to 3 Lines (Central, Northern, Waterloo) which in turn are only associated to 1 Branch each – so Bank is associated to 3 Branches
  • Acton Town Station is associated to 2 Lines (District and Piccadilly); District is associated to 1 Branch which Piccadilly is associated to 2 Branches – so therefore Acton Town is associated to 3 Branches.

The list of possible Arrival Stations is based on the set of Stations associated to any of the Branches the Starting Station is associated to.

So for Amersham, we’re looking for all those Stations on the metropolitan branch 0 Branch

For Bank we’re looking at Stations on the central 0, northern 1 and waterloo 0 Branches

and for Acton Town, we’re looking at stations on the district 0, piccadilly 0 and piccadilly 1 Branches.

So first we need to find a way to

  1. Identify the Starting Station
  2. Identify the Branches the Starting Station is associated with
  3. Identify the Stations associated to these Branches.

Identifying the Arrival Stations

To start with, we need to capture the starting station, which we can do with a parameter

pStart

String parameter which is a List object that populates from the Station field when the work book is opened, and is defaulted to Bank.

For the rest, we’ll build up what we need step by step, so on a new sheet add Branch and Station to Rows and display the pStart parameter.

I’m first going to identify the possible Branches associated to the pStart station, and ‘spread’ this across all the stations in that Branch

Possible Branches

{FIXED [Branch] : MIN(IF [Station] = [pStart] THEN [Branch] END)}

If the Station in the row matches that in pStart, then get the Branch for that row, then ‘spread’ that across all the rows with the same Branch (via the {FIXED [Branch]: …. } statement.

Add this onto Rows and you’ll see the name of the Branch is listed against all the stations associated to the branch that the pStart station is related to

Now we can define a field to capture the stations that have a Possible Branch

Possible Destination Stations

IF NOT ISNULL([Possible Branches]) THEN [Station] END

Add this to Rows too, and stations should only be listed against those rows with a Possible Branch

We can use this field to then create a Set. Right click on Possible Destination Stations > Create > Set

Destination Stations Set

Select Epping from the list displayed

Add the field to the Colour shelf (the Epping row should be coloured IN the set). Then click on the pill on the Colour shelf and select Show Set

The list of possible options in the Destination Stations Set should be displayed. Change the control type to be single value dropdown

Now test the behaviour of the set by changing the value of the pStart parameter eg select Amersham. Epping remains selected but is now contained in ( ) as it’s not a valid value. The other options to select though should all now have changed.

This is the ‘relative values’ only type behaviour required.

Determining the number of stops

While we’re working with a ‘check sheet’, let’s finalise the other calculations we’re going to need to build the final viz; firstly the number of stops between the two selected stations. We’re going to use the Path Order field to help with this.

Firstly, if it’s appearing as a string in the data set, convert it to a numeric whole number field, then add it to Rows between Branch and Station It should be a discrete dimension (blue disaggregated field). A unique number should be listed against each record; this record is effectively an index defining the order of the Stations on the Branch.

Let’s reset the station parameters to start at Bank and end at Epping These stations are on the Central 0 Branch, and Bank is at Path Order 47 and Epping at 61

The number of stations is the absolute difference between these two numbers. To determine this, we need to capture the Path Order for the starting station against every row.

Now, it’s possible that the stations are on multiple branches, so we need to make sure we have a handle on the Branch we care about

Selected Branch

{FIXED: MIN(IF [Destination Stations Set] THEN [Branch] END)}

Get the Branch associated to the selected destination station, and then ‘spread this’ across all rows.

Add this to Rows.

Now we can get the number associated to the pStart station on the Selected Branch, and spread this across every row

Starting Station Path No

INT({FIXED: MIN(IF [pStart] = [Station] AND [Branch] = [Selected Branch] THEN [Path Order] END)})

as well as

Destination Station Path No

INT({FIXED: MIN(IF [Destination Stations Set] AND [Branch]=[Selected Branch] THEN [Path Order] END)})

Add both of these as discrete dimensions to Rows

Then we can create

No. of Stops

ABS([Starting Station Path No] – [Destination Station Path No])

which is just the absolute difference between the two

Identifying the stations between start & end

The final piece of the puzzle, that we’re going to need is just to isolate all the Stations on the Branch that lie between the pStart station and the station in the Destinations Station Set. As this is going to be used to highlight the section of line on the map, I called this

Highlight Line

[Path Order] >= MIN([Starting Station Path No],[Destination Station Path No]) AND [Path Order] <= MAX([Starting Station Path No], [Destination Station Path No])

Here I utilised the rarely used (at least in my case) feature of the MIN and MAX functions, that allows you to supply multiple values and return a single value – the MIN or the MAX of the options provided. So in this case, I want to flag all the rows as being true if the Path Order sits between the Starting Station Path No and the Destination Station Path No. Add this onto Colour instead of the In/Out set and we can see all the rows between the two endpoints are highlighted.

Test by trying different start and ends, so you’re happy how the behaviour is working.

Building the tube map

This did take a bit of time to get right, and I did end up referring to Tableau’s own KB article on creating paths between origin and destination to get some pointers (although I didn’t follow it to the letter…)

Create a new sheet, then create a spatial field

Station Location

MAKEPOINT([Right Latitude], [Right Longitude])

and double click to automatically add the field to the new sheet. Longitude and Latitude fields are automatically generated and a basic layout is immediately visible

Add Branch to Detail then change the mark type to Line.

Add Path Order to Path. The lines should all now join up as expected

Delete all the text from the Tooltip, but ensure Show Tooltip is still enabled.

Set the background of the map to dark (Map menu > Background Maps > Dark). Adjust the Colour of the line to whatever suits (I used #01e6ff)

Add a 2nd map layer – drag Station Location onto the canvas and drop when the Add a marks layer option appears

Change the Mark type of this 2nd marks card to circle, then add Station and Line to the Detail shelf. Change the colour to same as the line and adjust the Size if required. Update the Tooltip as required.

To highlight the stations between those selected, create a new spatial field, just for those stations

Selected Stations

IF [Highlight Line] THEN [Station Location] END

Drag this on to the canvas to make a 3rd marks layer.

Add Branch to Detail, change the Mark type to line and add Path Order to Path. Change the Colour to something contrasting (I chose #ff00ff). Adjust the Size so the line is a bit thicker than the other lines.

To label the start & end station, create

Label – Stations

IF [Station] = [pStart] OR [Destination Stations Set] THEN [Station] END

Add to the Label shelf, and change to be an attribute (rather than dimension) so it doesn’t break up the line. Adjust the font accordingly. I set it to Tableau Medium 8pt bold in white, aligned top centre. All the labels to overlap other marks.

Show the pStart parameter and the Destination Stations Set list (just right click on the field in the data pane on the left and select Show Set – this is now an option as there are fields already on the viz that reference that set). Test the display by changing the options.

Add No of Stops to the Detail shelf, then update the title to reference the field. Set the font to white and align right.

Format the background of the whole worksheet to black, remove row/column dividers. Hide the null indicator field, and remove all map options (Map menu > map options, uncheck all the fields).

The viz should now be ready.

Add it onto a dashboard, which is also formatted to have a black background. Display the pStart parameter and the Destination Stations Set as floating objects. Update the title of each and format the latter so it has a black shading to the body of the control. Remove the ‘all’ option from the arrival station control (customise > uncheck show ‘all’ value).

My published version is here. Hopefully I’ve built it in a way that supports the impending Part 2…

Happy vizzin’!

Donna

Can you combine categorical & sequential colours in the same chart?

Donna ColesLeave a comment

For this week’s challenge, Kyle aimed to solve a problem that he discussed with a #TC24 attendee while waiting in line for a session. How to show solid (categorical) and graduating (sequential) colours within the same chart.

For this we’re going to create 2 sheets, one displaying Sales and one for Profit Ratio.

Data Preparation

As the requirements referenced the use of the Manufacturer field, I connected to the Superstore Sales.tds file I had stored locally, rather than the .xls file, as Manufacturer doesn’t exist in the xls file.

Once connected, I also added a data source filter where Sub-Category = Storage (right click on data source > edit data source filter).

Building the Sales bar chart

Add Manufacturer to Rows and Sales to Columns and sort descending. Format Sales to be $ with 0 dp, and show mark labels.

Create a new field

Colour – Sales Bracket

IF SUM([Sales]) > 15000 THEN ‘>$15k’
ELSEIF SUM(Sales) >= 4000 THEN ‘$4k-$15k’
ELSE ‘<$4k’
END

Then create another field

Colour – Sales Range

IF SUM([Sales])<4000 THEN SUM([Sales])*-1 END

By default, this will be a continuous field as it returns a numeric value. But when having multiple fields on the Colour shelf, which is what we’re going to do, the fields need to be discrete. So convert Colour – Sales Range to discrete (right click on field).

You also might be wondering why we’re multiplying the value by -1. This is to ensure the values when listed in the colour legend are sorted in the way we want. This will become clearer shortly.

Add Colour – Sales Range to the Detail shelf, then click on the icon to the left of the pill, and select the Colour icon, to add this pill to the Colour shelf along with the Colour – Sales Bracket field.

Re-order the pills so Colour – Sales Range is listed first.

Your viz will look something like this

To change the colours without having to go through each legend option individually, edit the colour legend and choose a sequential colour palette that suits. In my case I used a custom one I had installed, but you could just opt for the Red-Gold that should be installed by default.

Click the Assign Palette button, and Tableau will automatically assign the colours in the graduated sequence, which is why the ordering of the entries matters. The colour legend lists the entries in ascending order, and as we want to display the values in descending order, multiplying by -1 reverses the order for the colour legend.

Manually set colours for the NULL, >$15k and NULL, $4k-$15k options. Add a pale grey border around the marks (via the Colour shelf). Then adjust the tooltip, remove the Manufacturer column heading label, hide the axis and remove all gridlines, zero lines, axis rulers, row/column dividers, and update the title.

Note – I chose to re-order how the two colour pills were listed on the Colour shelf so that there was a more noticeable difference between the colour chosen for the $4k-$15k range vs the first entry for the <$4k range.

Building the Profit Ratio bar chart

For this you will need similar fields

Colour – PR Bracket

IF [Profit Ratio] > 0.15 THEN ‘>15%’
ELSEIF [Profit Ratio] >= 0 THEN ‘0-15%’
ELSE ‘<0%’
END

and

Colour – PR Range

IF [Profit Ratio]<0 THEN [Profit Ratio]*-1 END

and Profit Ratio should be formatted to % with 1 dp.

You then just need to go through similar steps to that described above.

Building the dashboard

I used a horizontal container to position the two sheets in, side-by side. I set the outer padding of each sheet to 0 and inner padding to 5 and set to fit entire view. Between the sheets I added a blank object which I set to have outer padding of 0. I set the background colour of this blank object to a mid grey, then set the width to be 2, which produces the thin divider line.

To enable the highlighting between the sheets ‘on click’ I simply selected all fields from the highlight menu button.

A relatively short blog this week, but a great concept that’s worth knowing. My published viz is here.

Happy vizzin’!

Donna

Can you expand this segmented bar chart?

Donna Coles2 Comments

For this week’s challenge I expanded on my challenge from week 13. So for this solution guide, I’ll be starting with the workbook I built for week 13 and adjusting as required. You can either build on your own solution if you took part, start with my workbook, or rebuild from scratch (my solution guide to week 13 is here).

Based on the hint I provided in the challenge, I’m going to build this with 3 sheets; one sheet to display the bar charts per year for the specific Sub-Category, then one sheet to display the segmented bars up to the selected Sub-Category and one sheet to display the segmented bars below the selected Sub-Category.

For this we need to assign an index to each row of data.

Sub-Cat Index

INDEX()

Make this discrete and add it to Rows in front of Sub-Category and adjust the table calculation so that it computes using Sub-Category only. Each row should be numbered from 1 – 17.

Creating the top & bottom bar chart

We will need to be able to identify the name of the Sub-Category selected and the associated index value. We’ll use parameters to store this information

pSelectedSubCat

string parameter defaulted to ” <empty string>

and

pIndex

integer parameter defaulted to a very large number, in this case I chose 10,000 (basically a number higher than the number of dimensions listed.

Show the parameters, and manually type into pSelectedSubCat the word ‘Copiers’ and into the pIndex type 8 (the index associated with Copiers).

We want to display an arrow indicator based on whether a Sub-Category is selected or not. We need

Arrow Indicator

IF [pSelectedSubCat] = [Sub-Category] THEN ‘▼’ ELSE ‘►’ END

Add this onto the Rows after the Sub-Category pill. Readjust the table calculation of the Sub-Cat Index so it is computing by Arrow Indicator as well.

We only want to show up to the selected Sub-Category

Show Top

[Sub-Cat Index]<=[pIndex]

Add this to the Filter shelf and set to True. Verify the table calculation is also computing by Sub-Category and Arrow Indicator only. Hide the Sub-Cat Index column and format the arrows so they are coloured brown and aligned top centre. Name this sheet Top or similar

Duplicate the sheet.

To show the bottom half of the chart create

Show Bottom

[Sub-Cat Index]>[pIndex]

Remove Show Top from Filter and add Show Bottom instead. Set to True. Again verify the table calculation is computing by Sub-Category and Arrow Indicator only. Call this sheet Bottom or similar.

Building the year bar chart

On a new sheet add Order Date to Rows and Sales to Columns. Change mark type to bar. Adjust Colour and size

Create a new field

Is Selected Sub-Cat

[Sub-Category] = [pSelectedSubCat]

Add to Filter and set to True.

Add another copy of Sales to Columns, then double click into the pill, and manually wrap the text with WINDOW_MAX(….. )

On the Window_Max marsk card, reduce the size to as small as possible and add a white border (via the Colour shelf). Add Sales to the Label shelf and adjust the font to be smaller and coloured brown.

Make the chart dual axis and synchronise the axis. Remove measure Names from the All marks card, and right click on the top axis and move marks to back. Hide both axes, remove row/column dividers and gridlines/zero lines. Adjust the font colour and size of the row labels, and remove the row header.

Add Sales to the Tooltip shelf of the All marks card, and add a quick table calculation of percent of total. Format to 1 dp. Add Sub-Category to the Tooltip shelf too, and then adjust the Tooltip of the All marks card to suit. Name the sheet Years or similar.

Building the dashboard

On the existing dashboard, add a vertical container, and then add the Top, Years and Bottom charts within it.

Remove the chart titles.

You’ll notice that the bar for bookcases in the bottom chart is longer than the bar for copiers in the top chart. We don’t want this. To correct we need

Max Sales Axis

{MAX({FIXED [Sub-Category]:SUM([Sales])})}

Navigate to the Top sheet and add Max Sales Axis to the Detail shelf of the All marks card

Create a new parameter

pMaxAxis

float parameter defaulted to 500,000

On the Bottom sheet, show the Sales axis, then edit the axis and adjust so the axis range is Custom from 0 to pMaxAxis

Hide the Sales axis again

Adding the Interactivity

We will use parameter actions to set the various parameters on click, but we need to ensure the viz will ‘expand’ but also fully ‘collapse’. So we need

Sub-Cat to Pass

IF [Is Selected Sub-Cat] THEN ” ELSE [Sub-Category] END

Index to Pass

IF MIN([Is Selected Sub-Cat]) THEN 10000 ELSE [Sub-Cat Index] END

ie if we’re selecting the Sub-Category that’s already been selected then we need to ‘reset’ back to the start state.

Add both of these fields to the Detail shelf of the All marks card on both the Top and Bottom sheets.

Change Sub-Cat to Pass to be an Attribute so that it doesn’t impact the existing table calc settings, and verify Index to Pass is computing by Sub-Category and Arrow Indicator.

Back to the dashboard, and add the following dashboard action

Select SubCategory

On select of the Top or Bottom sheets, set the pSelectedSubCat parameter, passing in the value from the Sub-Cat To Pass field. When clearing the selection, reset to ” <empty string>

Set Index

On select of the Top or Bottom sheets, set the pIndex parameter, passing in the value from the Index To Pass field aggregated at the minimum level. When clearing the selection, reset to 10,000

Set Axis

On select of the Top sheet, set the pMaxAxis parameter passing in the value from the Max Sales for Axis field. When cleared reset to 50,000

You should now have a functioning chart. You may need to adjust padding of the objects and modify width of headers on the charts to get the alignment as required. Update the chart title as required as well.

My published viz is here.

Happy vizzin’!

Donna

Sleep Survey Marimekko

Donna ColesLeave a comment

Week 18 2024, was the #Data24 TC edition, which we shared live during our session at TC. It was so wonderful to see so many people at our session, and to be able to meet one of our new coaches, Yusuke Nakanishi, in person.

Sean set the challenge to recreate a Marimekko chart based on some sleep survey data.

Let’s dive in…

Setting up the data

The data provided contained a row per respondent indicating the hours slept and their responses to 4 questions. For this viz, we only care about the data associated to the Wake Up Well Rested and Sleep Hours fields.

The hours of sleep is grouped into custom bins

Hours Bin

IF [Sleep Hours] <= 4 THEN “<= 4” ELSEIF [Sleep Hours] >= 10 THEN “10+”
ELSE STR([Sleep Hours])
END

On a new sheet add Hours Bin to Rows and Wake up Well rested to Columns and Sleep Survey.csv (Count) to Text. Manually adjust the ordering of the Hours Bin field, so it is displayed in the correct sequence. This gives us the number of respondents per bin per response.

For the y-axis, we’re looking to display the % of total of respondents for each bin

% of Total

COUNT([Sleep Survey.csv]) / TOTAL(COUNT([Sleep Survey.csv]))

Add this into the table, and adjust the table calculation to ensure it is computing by Wake up Well rested only

This should mean each row adds up to 100% (you can check by displaying row grand totals).

Now for the x-axis, we need to plot a point based on how wide each bar needs to be. This width is based on the total number of respondents in each Hours Bin

Count People per Bin

{FIXED [Hours Bin]: COUNT([Sleep Survey.csv])}

Add to the table, and you should see the values are the same for each Hours Bin regardless of the response column.

But if we plotted at these points, we won’t get the points in the right place, we need a further step… we need to plot at the cumulative values. So add a Running Total Quick Table Calculation to the Count People per Bin field, that is set to compute by the Hours Bin only.

We now have the core fields we need to build the chart.

Building the Marimekko chart

On a new sheet, add Hours Bin to Detail. Add % of Total to Rows and Count People per Bin to Rows. Manually sort the Hours Bin field so that it is logically ordered.

Add Wake up Well Rested to Colour and adjust accordingly, and manually reorder the values in the colour legend.

Adjust the table calculation of the % of Total field, so that is computing by Wake up Well rested only – all the bars should extend to 100%

Then adjust the table calculation of the Count People per Bin Running Total so that its is computing by Hours Bin only – the bars should shift, into their expected logical position based on the Hours Bin ordering – ie <=4 is the first bar and 10+ is the last.

Now to get the bars to be wider, add Count People per Bin to the Size shelf, then as we have measures (green pills) on both the rows and column shelves, we can set the size to be Fixed and Right aligned, to get the display we require

Hide the Tooltips, hide the bottom axis, and remove the title of the y-axis, and you should have the required display.

When I added to the dashboard, I just used text boxes carefully positioned to simulate the labelling of the x-axis, and then I floated text boxes on top of the chart to label the sections.

My published viz is here.

Happy vizzin’!

Donna

How far would total steps from TC23 respondents take you?

Donna ColesLeave a comment

As community month draws to a close, long term participant Deborah Simmonds set us a map based challenge based on TC23 data.

I chose to use the data that was associated to the solution workbook, rather than take the direct source, so bear that in mind if you’re following along.

Examining the data

As there were quite a few fields in the data set which were directly related to the solution, I just wanted to familiarise myself with what I was working with initially. Essentially we have a row per person (Names Full) with the hotel they stayed at (Label Hotels), and how many steps (Steps) they took each day (Date). The details of the convention centre (Label Convention Centre) exist against every row.

The LAT and LON fields contain the location details of the hotels, while Convention LAT and Convention LON contain the location details of the Mandalay Bay Convention Centre.

Names Full already had the logic applied to create names for users based on their User ID if the Name was NULL, so I didn’t need to do anything for that requirement.

We only want to consider records relating to those people who attended ‘In Person’ and who had provided a hotel. Consequently I added the following as data source filters (right click data source -> edit data source filters) to exclude unrequired records from the whole analysis.

  • Did you attend in person or virtual? : In person
  • Label Hotels : excludes NULL

Building the BANs

The BANs have 3 metrics – the number of attendees, the number of steps, and the distance in metres (based on the logic that 1 step = 0.75m).

Attendees

COUNTD([User ID])

Distance (m)

[Steps] * 0.75

On a new sheet add Date to Filter as a range of dates and show the filter control. Add Attendees, Steps and Distance (m) so the measures are displayed in a row.

Change the mark type to shape and add a transparent shape (see this blog for more details). Add Measure Names to Label too and then adjust label accordingly and align centrally.

Hide the header row (uncheck show header), remove row dividers and don’t show tooltips. Name the sheet BANs or similar. Adjust the date slider and the values should adjust.

Building the bar chart (viz in tooltip)

On a new sheet add Names Full to Rows and Steps to Columns. Apply the same Date filter (from the BANs worksheet, set the filter to apply to this new worksheet too).

The viz needs to display a reference line showing the overall average of the steps per person across the selected dates, regardless as to whether the records are filtered to a hotel or not.

What do I mean by this… well if I add a standard average reference line to the viz above, the average for the whole table is 78.2k steps.

If I now filter this by a Label Hotel, then the average changes, and I don’t want that – I still want to see 78.2k.

But the average does need to change if the date range changes

This took a bit of effort to get right, but I needed

Avg Steps

SUM({FIXED Date: SUM([Steps])})/SUM({FIXED:COUNTD([User ID])})

Format this as a number with 1dp set to the K (thousandths) level

and I also needed to add the Date field on the Filter shelf to context.

So reverting back to the initial view of the bar chart… right click on the Date filter and Add to Context. Add Avg Steps to the Detail shelf. Then add a reference line (right click the Steps axis > add reference line) that displays the Avg Steps with a dark dashed line with a custom label.

Format the reference line to position the label at the top and adjust the font style.

To colour the bars we need

Steps above average

SUM([Steps]) >=[Avg Steps]

Add this to the Colour shelf, change the colours and adjust the opacity to about 75%.

Hide the column heading, and adjust the font size of the names and the axis. Name the sheet Bars or similar.

Building the initial map

I decided to create 2 maps – one for the initial display of all the hotels and the convention centre, and then one for the selected location and buffer.

To plot the hotels on the map I created

Hotel Locations

MAKEPOINT([LAT],[LON])

On a new sheet, add this to the Detail shelf. A map will automatically generate with Latitude and Longitude fields. Change the Mark Type to Circle, then add Label Hotels to Label and align left middle. Edit the map Background Layers (via the map menu) to add Streets, Highways etc to the display

Add Steps, Attendees and Distance(m) to the Tooltip. Add Attendees to the Size shelf and adjust the size to vary by range

Apply the Date filter from the other worksheets to this sheet too. For the tooltip, We need to know about the min and max dates in the range selected. Create

Min Date

MIN([Date])

and custom format simply as dd (the day only)

Also create

Max Date

MAX([Date])

and custom form this as dd mmm yyyy

Add both of these fields to the Tooltip shelf. Adjust the text in the Tooltip and add a reference to the Bars sheet as a viz in tooltip (Insert > sheets > ). Adjust the height of the sheet to be 900.

The viz in tooltip should now display nicely on hover

To add the mark for the convention centre, we need

Conf Location

MAKEPOINT([Convention LAT], [Convention LON])

Drag this onto the map, and drop it when Add A Marks Layer displays

This will create a 2nd marks card. Change the mark type to shape and select the Tableau sparkle image if you have it stored. If not, just use another shape or circle (coloured differently).

Add Attendees to Size and add Label Convention Centre to Label and align left middle. Add Steps and Distance(m) to Tooltip and adjust to suit. Hide all the map options (map menu -> map options -> uncheck all the selections). Name the sheet Map – Initial or similar.

Building the ‘selected’ map

This will use parameters to identify what’s been selected – a hotel or the convention centre, so we need

pSelectedHotel

string parameter defaulted to empty string

and

pSelectedCentre

string parameter defaulted to empty string, just like above

The intention is that either both these parameters will be empty or only one will be populated.

To plot on a map we need

Selected Hotel Location

IF [pSelectedHotel] = [Label Hotels] AND [pSelectedCentre] =” THEN [Hotel Locations] END

and

Selected Centre Location

IF [pSelectedHotel] = ” AND [pSelectedCentre] =[Label Convention Centre] THEN [Conf Location] END

Duplicate the initial map sheet and name it Map – Selection. Show the two parameters and verify both are empty.

On the Hotel Locations marks card, drag the Selected Hotel Location field and drop it straight onto the Hotel Locations field. On the Conf Location marks card, drag Selected Centre Location and drop straight onto the Conf Locations field. Your map shouldn’t display anything…

Manually type ‘Luxor’ into the pSelectedHotel parameter. A mark should display. Adjust the Label of the Selected Hotel marks card so it is larger font, and aligned top middle. Set the colour to orange and remove the halo.

Remove the text from the pSelectedHotel parameter and manually type ‘Mandalay Bay Convention Centre’ into the pSelectedCentre parameter. A mark should display. Adjust the Label of the Selected Centre marks card so it is larger font, and aligned top middle. Remove the halo from the Colour shelf.

To create the buffer circle, we need to define the buffer radius, which is the attendee steps in metres.

Buffer Distance (m)

{FIXED : SUM(IF [pSelectedHotel] = [Label Hotels] OR [pSelectedCentre] = [Label Convention Centre] THEN [Steps] END)} * 0.75

and then we create

Buffer

IF [pSelectedHotel] <> ” THEN
BUFFER([Selected Hotel Location], [Buffer Distance (m)], ‘m’)
ELSEIF [pSelectedCentre] <> ” THEN
BUFFER([Selected Centre Location], [Buffer Distance (m)], ‘m’)
END

Add this as another marks layer.

Reduce the opacity on the colour shelf to 0% and set the border to be orange. We don’t want to allow any interactivity with the buffer, so disable selection of the layer, and also move down so it is listed at the bottom of the 3 map layers.

Show the Date filter control and adjust the dates to see the buffer adjusting. Test the behaviour with a hotel too (you may find you want to add some more detail to the background layers of the map).

We will use dynamic zone visibility on the dashboard to decide whether to display the initial or the selected map. To control this, we need

Show Initial Map

[pSelectedCentre]=” and [pSelectedHotel]=”

and

Show Selected Map

[pSelectedHotel]<>” OR [pSelectedCentre]<>”

Adding the interactivity

Create a dashboard, add the BANs and both the map sheets.

Create a dashboard parameter action

Set Hotel

On selection of the Initial Map set the pSelectedHotel parameter passing in the value from the Label Hotels field. When the selection is cleared, reset to ”.

and another parameter action

Set Conv Centre

On selection of the Initial Map set the pSelectedCentre parameter passing in the value from the Label Convention Centre field. When the selection is cleared, reset to ”.

Select the Initial Map object, and from the Layout tab, set the visibility to be controlled by the Show Initial Map field

Then select the Selected Map object, and set the visibility to be controlled by the Show Selected Map field. Only one of the maps should display based on the interactivity.

This is all the core functionality of the map, but Deborah threw in a couple of extra asks…

Building the Distance Legend

We’re using map layers again for this. Create a new field

Zero

MAKEPOINT(0,0)

Add it to the Detail shelf of a new worksheet to create the 1st map layer, then immediately add another map layer, by adding another instance of Zero to the sheet.

Switch the axis, and the map will disappear, and you’ll have axis displayed instead. Change the mark type of the first map layer to circle and colour orange.

Change the mark type of the 2nd map later to circle and colour pale grey with an orange border. Increase the Size of this circle so it appears as a ring around the filled orange circle. Move the 2nd marks layer down to the bottom and disable both marks from being selectable.

Hide the axis and gridlines/zero lines.

Right click on the central circle and annotate point. Don’t enter any text into the annotation box, just click OK. You should get the annotation box with a line.

Move the box so the connector line is horizontal, then format the annotation so the shading is set to none and the line is formatted to be a darker dashed line. Update the title of the sheet.

Building the Size Legend

Apply a similar process to that described above, but this time create 3 mark layers where the mark type is an open circle shape which is coloured blue, and for each layer, the size of the circle is slightly bigger. This time show the zero lines.

Set the background of both the legend sheets to be none (ie transparent), then add them as floating objects onto the dashboard. Use the control visibility feature to only display the Size legend when Show Initial Map is set, and only display the Distance legend when Show Selected Map is set. Set a background against each object of light grey, that is then set to 80% transparency.

With this you should have a completed challenge. My published version is here.

Happy vizzin’!

Donna

Can you create a sales dashboard by effectively utilising headers?

Donna ColesLeave a comment

Community Month continues and this week Hideaki Yamamoto provided us with this 1 sheet view. He very kindly posted requirements aimed at different levels, but I’m writing the solution for the Level 1 version.

Identifying the Current & Previous FY

The dashboard is all focused on reporting over financial years, and Tableau very kindly allows us to set the start month of the FY, in this case April. Right click on Order Date -> Default Properties -> Fiscal Year Start

If you now double click on Order Date to add it to a new sheet, you automatically get the discrete YEAR part of the Order Date displayed with the relevant FY label.

Expand the field to show the quarter & month, then create an explicit field

Year Order Date

YEAR([Order Date])

convert to discrete, and format as a number with 0dp and no thousand separators.

Add this to the display too and you can see how the dates behave…. every FY start with the month of April, but the FY label is based on the year of the last month (ie March), so FY2024 contains data from April 2023 to March 2024.

Throughout the challenge though, the FY is displayed in the FYXXXX-XX format, and there doesn’t seem to be a way to get a handle on the formatting Tableau applies when the fiscal year is set. So I had to come up with a calculated field to get the FY to display as I wanted.

FY Display

IF MONTH([Order Date])>=4 THEN “FY” + STR(YEAR([Order Date])) + “-” + RIGHT(STR(YEAR([Order Date])+1),2)
ELSE “FY” + STR(YEAR([Order Date])-1) + “-” + RIGHT(STR(YEAR([Order Date])),2) END

Add this to the display (remove the quarter field too).

With this we can identify the maximum FY display (as there’s a requirement not to hardcode anything).

Max FY

{MAX([FY Display])}

Add this to the table

Now we can create a parameter which will display the values of FY Display and automatically show the latest/maximum value by default. Right click on FY Display -> Create -> Parameter

pSelectedFY

string parameter that sets the value to Max FY when workbook opened and lists the value when the workbook opens from the FY Display field.

To identify what records relate to the current or previous year, we create

FY End Year

INT(RIGHT([FY Display],2))

which returns the last two numbers of the FY Display string. Make this a dimension. Then we can get whether the row is related to the FY selected in the parameter by

Is Current Year

INT(RIGHT([pSelectedFY],2)) = [FY End Year]

and

Is Previous Year

INT(RIGHT([pSelectedFY],2))-1 = [FY End Year]

Add the fields onto the table, and show the pSelectedFY parameter

Adjust the parameter to see how the values change. We can now create a field that we can use to filter the data to the rows we’ll need – ie just those for the current year or the previous year

Dates to Display

[Is Current Year] OR [Is Previous Year]

It feels like I created a lot of fields just to get to this point…. there’s probably a more efficient route, but that’s just where my logical next step went to as I built out what I thought I’d need…

Building the basic chart

On a new sheet, add Dates to Display to Filter and set to True.

Add Order Date to Columns and set to the discrete Month level (blue pill). Add Sales to Rows. Add Is Current Year to Colour and adjust accordingly. Re-order so True is listed first.

We need to split the chart by Region, but the headings need to be adjusted based on which region is going to be selected. The selected Region will be stored in a parameter

pSelectedRegion

string parameter defaulted to ‘East’

Show this parameter on the sheet and then create a new field

Region to Display

IF [Region] = [pSelectedRegion] THEN ‘▼’ + [Region]
ELSE ‘►’ + [Region]
END

Add this field in front of MONTH(Order Date) on Columns and Sort based on Region ascending.

We need to show the cumulative sales. We can do this with a quick table calculation on the Sales pill, but sometimes like to create an explicit field, so I know exactly what pill is what

Running Sum Sales

RUNNING_SUM(SUM([Sales]))

This is the same code that a quick table calculation will generate. Format to $ with 0 dp.

Replace the Sales field on Rows with Running Sum Sales and adjust the table calculation setting, so it is computing by Month of Order Date only. Add Order Date to Detail too.

Now, the required solution at Level 3 shows the line (above), the area underneath coloured, and circular markers on the line where the end point is larger than the rest. This ‘feels’ like 3 different marks – line, area and circle, but we can’t do more than 2 mark types with dual axis….

…but we can ‘fake’ it. Now getting the large circle to display was actually part of the challenge that got me stumped, and that I ended up applying at the end after mulling it over with my colleague, Sam Parsons. For the purposes of this blog though, it’s easier to add the relevant logic now.

We want to identify the value associated to the last point for the current year

Last Sales Value

IF LAST() = 0 AND ATTR([Is Current Year]) THEN RUNNING_SUM(SUM([Sales])) END

Drag this onto the Running Sum Sales axis, and drop it when the two green columns appear

This will automatically add Measure Names and Measure Values to the sheet. Move Measure Names from Columns to Detail. Change the mark type explicitly to line. Adjust the table calculation settings of the Last Sales Value field so it is computing by Month of Order Date only. You should notice the end of each ‘current year’ line has a little circle. It’s still a line mark type, but as it’s only 1 point it has no other points to join up to, so looks lie a circle.

We want it to be more prominent though, so move Measure Names from Detail to Size. Reorder the size the fields on the size legend, so the Last Sales Value is bigger. Then from the Colour shelf, add markers to lines

Add another instance of Running Sum Sales to the Rows shelf. This will create a 2nd marks card. Change the mark type of this to Area. Remove Measure Names from this marks card, and adjust the Colour to have an opacity of around 30%. Turn stack marks off (Analysis Menu ->Stack Marks -> off). Set the chart to dual axis and synchronise axis.

Hide the right hand axis, and rename the title of the left hand axis. Format the axis to be $ with 0 dp.

On the bottom half of the chart, we want to display the current year sales as bars with previous year as a reference line, so we need

Sales – CY

IF [Is Current Year] THEN [Sales] END

and

Sales – PY

IF [Is Previous Year] THEN [Sales] END

Format both to $ with 0dp.

Add Sales – CY to Rows which will add a 3rd marks card. Change the mark type to Bar.

Add Sales – PY to Detail. The right click on the Sales – CY axis and Add Reference Line.

Set the reference line to be per cell based on the Sales-PY field, formatted as a line and with a fill below of light grey to give the appearance of a bar.

Change the title of the Sales – CY axis. Remove all gridlines and zero lines. Format the MONTH(Order Date) to use 1st letter only. Hide the null indicator.

Adding the ‘headers’

We need to have 4 rows of headers at the top – currently we’ve got 1 – the Region.

Below Region we want to split the data by Category if its the selected region, so we need

Category to Display

IIF([Region]=[pSelectedRegion], [Category],”)

Add this on to Columns after Region to Display. The visuals should automatically adapt. Adjust the value of the pSelectedRegion parameter to see how the viz changes.

Now double click into the Columns shelf and manually type ‘Total Sales’ (including the quotes). This will create a ‘dummy’ header pill. Move it to be after Category To Display.

Finally, create a new field

Current Year Sales

{FIXED [Region], [Category To Display]: SUM([Sales – CY])}

change this to be a dimension and format to $ with 0dp. Add this field to Columns after Total Sales, and we now have all the header fields we need.

Change the formatting as follows

  • Region To Display : Shading navy, font white, size 12, Tableau Medium Bold
  • Category to Display : font black, Tableau Medium size 12
  • Current Year Sales : font dark teal, Tableau Medium size 14 bold

Adjust the width of each header row to give a bit more ‘breathing room’.

Format the column dividers so the Header level is set to a thick white line, and set the row divider so the header level is set to None

Hide the ‘Region To Display / Category To Display / ‘Total Sales’/… etc heading label (right click and hide field labels for columns). Adjust the font of both axis to be smaller (I set to 8pt).

Adding the Tooltips

Add FY Display, Year Order Date, Region, Sales and Category To Display to the Tooltip shelf of the All marks card.

We need another couple of fields to get the required display.

Month Order Date

MONTH([Order Date])

convert to a dimension and custom format as 00

Tooltip |

IF [Category To Display] <> ” THEN ‘|’ END

Add these fields to the Tooltip shelf too of the All marks card and adjust the tooltip

Adding the sheet title

For the sheet title, we need to display the FY of the previous year

FY Display Prev Year

‘FY’ + STR(INT(MID([pSelectedFY],3,4))-1) + ‘-‘ + STR(INT(RIGHT([pSelectedFY],2))-1)

Add this to the Detail shelf of the All marks card. Then adjust the title of the sheet so its referencing the pSelectedFY parameter and the FY Display Prev Year field.

Adding the interactivity

Add the sheet onto a dashboard. I floated the pSelectedFY parameter and displayed it as a slider but customised to not show the slider.

Create a single dashboard parameter action to select the Region

Set Region

On select of the viz, set the pSelectedRegion parameter passing in the Region field. Set the value to empty when selection is cleared.

And with that, you should have a completed solution. My published viz is here.

Happy vizzin’!

Donna

Can you build a trellis of Profit Ratio Gauges?

Donna Coles3 Comments

It’s Community Month at #WOW2024 HQ, and for the first challenge, Jack Hineman (a long time participant of WOW) asked us to create gauge charts using map layers. Not only that, he wanted them displayed in a trellis format, with a specific requirement to ensure the number of columns was always >= number of rows displayed. Errr…..

This was tough! I tried to start the challenge one evening and after pouring through Ken Flerlages’s blog post that was referenced, reading the hints, and looking at Jessica Moon’s Tableau Public page referenced, I was none the wiser. The blog post did not mention map layers at all in building a gauge, and was purely mentioned as inspiration for the design of the gauge.

I happen to have some time off work, so reattempted the challenge the next day. After several hours, I got there, somehow! I did another google : “Gauge charts in Tableau” and hit upon this blog, which gave me a few pointers, although most of the time it really was a lot of trial and error based on Jack’s hints, and to be honest I surprised myself that I actually hit all the requirements, except one, without the need to look at the solution.

The one thing I had to look at was the calculations required to get the trellis to always have more columns than rows. My ‘go to’ formula didn’t work. More on that later.

As for my solution… well it’s a solution…. how elegant/efficient it is – who knows. It was something built very much in stages as I tried to get my head around what was being asked. As I rebuild as part of the process I go through when writing this blog, I may find ways of improving what I did to start with. I will do my best to explain what I think is going on, what my thought process was, but apologise in advance if you get to the end of all this, and still don’t have a ‘scooby’ 😦

Strap yourself in! This is going to be a long one!!!!

Modelling the data

Connect to the provided data source and create relationship calculations to relate Manufacturers to Gauge_Definition with 1=1 and Gauge_Definition to Gauge_Points with 1=1 as well.

Understanding the data

Let’s start by looking at the data provided. Jack provided 3 data sets:

Manufacturers

A simplified instance of Superstore just listing Manufacturers with Sales & Profit data.

Gauge Definition

A data set of 1 row essentially containing some ‘constants’ to be referenced within the challenge

  • Gauge Success Amount = 0.15
    • Profit Ratios >= to 0.15 (15%) are deemed successful. This is essentially the Goal indicator value.
  • Gauge Concern Amount = 0
    • Profit Ratios >=0 (but < 0.15) are deemed a concern
  • Gauge Start Profit Ratio = -0.3
    • The left hand point on the semi-circular gauge should indicate a -30% profit ratio
  • Gauge End Profit Ration = 0.3
    • The right hand point on the semi-circular gauge should indicate a 30% profit ratio
  • Gauge Success Pct of Gauge = 0.75
    • 15% Profit Ratio represents 75% of the gauge displayed (ie 0% of gauge = -30% Profit Ratio and 100% of gauge = 30% profit ratio)
  • Gauge Concern Pct of Gauge = 0.5
    • 0% Profit Ratio represents 50% of the gauge displayed.

Gauge Points

This is essentially a template/scaffold to help build the gauge and the various features on the gauge. It defines all the points that need to be plotted and in most cases then connected to create the various ‘shapes’ displayed eg the gauge semi circle for the actual profit ratio and the legend indicator; the small angled rectangle that represents the goal ‘reference line’; the positions for the 3 labels.

To start getting an understanding, let’s just focus on Point Type = Actual and Point Segment ID = Background

This is the data to build the complete grey semi circle of the main gauge. 50 rows represent points on the inner arc of the semi circle (Point Arc = In). They all have a Point Radius = 0.43 (ie the distance from the 0,0 centre position of a circle to the bottom edge of the gauge is 0.43). The other 50 rows represent points on the outer arc of the semi circle (Point Arc = Out). They all have a Point Radius = 0.58 (ie the distance from the 0,0 centre position of a circle to the top edge of the gauge is 0.58). Point Angle Rads defines the angle in radians (rather than degrees) from the circle centre to edge of the circle. The Point ID defines the order to ‘join the dots’ when the points are made into a polygon.

Here’s a diagram to help try and explain the maths we’re going to need to use based on the data we have

For each point on the circle, we will need to identify the x & y position of where the radius intersects the edge of the circle. We know the angle, and we know the radius, so we can use trigonometry to work that out, and then use the MAKEPOINT() function in Tableau to covert that into a spatial/geometric field to use on a map.

Let’s do this in Tableau.

Create field

X

[Point Radius] *SIN([Point Angle Rads])

Y

[Point Radius] * COS([Point Angle Rads])

Note – based on my diagram above, X = a and would be derived from the Cosine of the angle, while Y = o and be based on the Sine of the angle. However, Jack gave hints based on the above calcs which hold true if you adjust the diagram and assume the angle is positioned between the y-axis and the radius, rather than the x-axis and the radius.

Now create the point

Geo

MAKEPOINT([X],[Y])

On a sheet, add Point Type to Filter and set to Actual, and add Point Segment ID to Filter and set to Background.

Double click on Geo to automatically add Longitude and Latitude fields to the sheet.

We have the basics of a semi-circle… not in the right direction, but it’s something… Add Point ID to Detail, then click the Swap Axis button and hey presto…

It appears a bit more ‘ovel’ than circular as the axis aren’t aligned, so don’t worry about this – set the display to Entire View will help. Then change the mark type to Line, move Point ID to Path and then change mark type to Polygon.

We now have a filled semi circle. We’re not going to use this sheet, but hopefully, this has helped a bit with some fundamental understanding.

When building we’re going to be using map layers (and at this point, we can’t add a layer to this sheet). We’ll also be defining calculations based on which feature of the viz we’re focussed on, as we can’t apply filters to the sheet (if you remove the ones applied, it will look a little crazy!). But if you change the Point Segment ID filter to Goal, you’ll get the shape of the goal indicator ‘reference line’.

You might want to play around with the filters to examine the behaviour.

Are you still with me…? Take a break, grab a cuppa, we haven’t even started building yet, but I’ll still be here when you get back 🙂

Setting up the map layers

Using Jack’s hints, create a field to help ‘initialise’ the map layers

Zero

MAKEPOINT(0,0)

Double click this to create a basic ‘map’ with a single point.

Then drag another instance of Zero onto the canvas and drop on the Add a Marks Layer section that appears

You’ll now have 2 marks layers, which means whatever we now do, we can always add more.

Building the Gauge Background & Legend layer

Note – in building I ended up with more mark layers than Jack suggested. I’ve subsequently seen other versions but am sticking to what I managed for now.

The first layer I’m going to build is the ‘grey’ semi circle of the main gauge and the coloured legend ‘inner’ semi circle.

I want to identify those points only.

Geo: BG-Legend Layer

IF [Point Type] <> ‘Label’ AND [Point Segment ID] IN (‘Background’, ‘Concern’, ‘Failure’, ‘Success’) THEN [Geo] END

On the Zero marks card, drop this field directly on top of the COLLECT(Zero) to replace it. Add Point ID to Detail, then flip the axis using the switch axis button. Add Point Segment ID to Colour and adjust the colours of the Background, Failure, Concern and Success values to suit.

Change the mark type to polygon, and move Point ID to Path. Name this layer BG & Legend

Building the Actual Layer

Create a new field

Profit Ratio

{FIXED [Manufacturer]: SUM([Profit])/SUM([Sales])}

and format to % with 1 dp.

We need to understand where on the gauge, the Profit Ratio for each Manufacturer falls. We know that the gauge starts at -30% Profit Ratio (ie 0% of the gauge is equivalent to -30% Profit Ratio) and the gauge ends at +30% Profit Ratio (ie 100% of the gauge is equivalent to +30% Profit Ratio). Therefore if the Manufacturer’s Profit Ratio >= 30% it fills 100% of the gauge, anything less needs to be partially through. We can calculate this (using one of Jack’s hints) with

PR % of Gauge

([Profit Ratio] + [Gauge End Profit Ratio]) / ([Gauge End Profit Ratio]- [Gauge Start Profit Ratio])

To then determine the angle in degrees (and again using Jack’s hints), we want to find the proportion of 180 degrees that the PR % of Gauge represents, and then take off 90 degrees based on the gauge rotation.

PR Angle (Degrees)

([PR % of Gauge] * 180)-90

We can then convert this to radians

PR Angle Rads

RADIANS([PR Angle (Degrees)])

This gives us information to help determine a singular point on the gauge we need to ‘draw’ up to. But I want to ‘draw’ a polygon that goes from the left end (-30% mark) to this point, and for this, I need to know all the other points up to that point.

The way I came up with isn’t what Jack did. My result means I don’t get an exactly accurate marker, but it is so close to it’s position, and is ‘good enough’ for the viz type and how it’s being displayed.

To understand what I did, build out a tabular sheet that is filtered to Manufacturer = Hon and Point Type = Actual, and shows Manufacturer, Point Segment ID and Point ID on Rows with Point Angle Rads, Profit Ratio and PR Angle Rads as measures.

The Profit Ratio for the Manufacturer =Hon is 6.4% which is at an angle of 0.34 radians around the semi circle.

You can also see that while we have values for the Point Angle Radians field associated to the Point Segment ID = Background, we don’t have any for the Point Segment ID = Actual, as this is what we’re trying to find out.

Given that I know that all the Point Angle Radians values associated to the Point Segment ID = Background build a complete semi circle, I figured, to display up to my ‘actual’ Profit Ratio, I just want to get all the points associated to background which are less than the PR Angle Rads value.

PR Point Angle Radians

IF [Point Angle Rads] <= [PR Angle Rads] THEN [Point Angle Rads] END

Pop this into the table, and when you scroll down, you’ll see I’ve only got values in my new field up to where the PR Angle Rads is less

Using this field, I’ll create some new X & Y fields which I can make make into a spatial field

X (actual)

[Point Radius] *SIN([PR Point Angle Radians])

Y (actual)

[Point Radius] * COS([PR Point Angle Radians])

Geo – Actual

IF [Point Type] = ‘Actual’ AND [Point Segment ID] = ‘Background’ THEN
MAKEPOINT([X (actual)], [Y (actual)])
END

On the Zero(2) marks card, Replace the COLLECT(Zero) pill with the Geo – Actual pill by dragging the latter and dropping it directly on the former. Add Point ID to Detail.

It’s drawing the points for a complete semi-circle, as every Manufacturer is being included. To help get the rest of the display right, for the various permutations, add Manufacturer to filter and filter to Hon, Bush, Logitech and Xerox. Add Manufacturer to Columns too. You should now see the marks stop at various positions around the arc. The filters will be adjusted later when we tackle the trellis.

Change the mark type to polygon and move Point ID to Path. Rename the layer to Actual.

For the colouring, we need to determine the RAG status of each Profit Ratio – where does the PR % of Gauge sit in comparison the constants we know

Actual RAG

IF [PR % of Gauge] <= [Gauge Concern Pct of Gauge] THEN ‘Failure’
ELSEIF [PR % of Gauge] <= [Gauge Success Pct of Gauge] THEN ‘Concern’
ELSE ‘Success’ END

Add this to the Colour shelf and adjust accordingly.

Building the Goal Indicator Layer

Create a new spatial field

Geo: Goal Layer

IF [Point Type] = ‘Actual’ AND [Point Segment ID] = ‘Goal’ THEN [Geo] END

and then drag this onto the canvas and drop on the Add A Marks Layer option.

Change the mark Type to Polygon and add Point ID to path. Add Point Segment ID to Colour and adjust the colour of the Goal value to suit. Rename the layer to Goal.

Building the Label layer

Create a new spatial field

Geo: Labels

IF [Point Type] = ‘Label’ THEN [Geo] END

and then drag this onto the canvas and drop on the Add A Marks Layer option. Add Point Segment ID to Detail. 3 marks should now displayed in the positions we need them

This bit took a bit of time to get right, as I had 3 requirements I wanted to satisfy: 1 – display a text or a numeric field as a label depending on what label I wanted to display (I attempted to have a single ‘label’ field converting numbers to strings with the relevant formatting, but the Profit Ratio % just wouldn’t show how I wanted when converted to string); 2 – adjust the colour of (some) of the labels depending on the RAG status of the profit ratio value; 3 – adjust the size of the Profit Ratio label based on how many gauges were displayed.

I needed several label fields

Label: Goal

IF [Point Segment ID] = ‘Goal’ THEN [Gauge Success Amt] END

formatted to % with 0 dp.

Label: Manufacturer-Fail

IF [Point Segment ID] = ‘Manufacturer’ AND [PR % of Gauge]<=[Gauge Concern Pct of Gauge] THEN [Manufacturer] END

Label: Manufacturer-Concern

IF [Point Segment ID] = ‘Manufacturer’ AND ([PR % of Gauge]<=[Gauge Success Pct of Gauge] AND [PR % of Gauge]>[Gauge Concern Pct of Gauge]) THEN [Manufacturer] END

Label: Manufacturer-Success

IF [Point Segment ID] = ‘Manufacturer’ AND [PR % of Gauge]>[Gauge Success Pct of Gauge] THEN [Manufacturer] END

Label: PR-Fail

IF [Point Segment ID] = ‘BAN’ AND [PR % of Gauge]<=[Gauge Concern Pct of Gauge] THEN [Profit Ratio] END

formatted to % with 1 dp

Label: PR-Concern

IF [Point Segment ID] = ‘BAN’ AND [PR % of Gauge]>[Gauge Concern Pct of Gauge] AND [PR % of Gauge]<= [Gauge Success Pct of Gauge] THEN [Profit Ratio] END

formatted to % with 1 dp

Label: PR-Success

IF [Point Segment ID] = ‘BAN’ AND [PR % of Gauge]>[Gauge Success Pct of Gauge] THEN [Profit Ratio] END

formatted to % with 1 dp

Add all these fields to the Label shelf, and adjust the label so that all are positioned on the same line, with no spaces, and add a carriage return beneath the text. Colour each field accordingly (DO NOT ADJUST THE FONT SIZE).

Change the mark type to text and align the label top centre. Rename the mark type to Labels and Disable Selection

To adjust the size of the labels, create a new parameter which we’ll need for the trellis.

pShowTop

integer parameter, defaulted to 7 which is a range from 5 to 81 with a step size of 1.

Show the parameter.

Create a new field

Label: Size

IF [Point Segment ID] = ‘BAN’ THEN [pShowTop]
ELSEIF [Point Segment ID] = ‘Manufacturer’ THEN 60
ELSE 80 END

We want the size of the BAN label to decrease as the number of manufacturers displayed increases.

Add this filed to the Size shelf as a continuous dimension (green pill, not aggregated).

Edit the Size legend so that sizes vary by range, the range is reversed, and the range starts from 1 to 81. Adjust the mark size range slider to a suitable start and spread.

As you change the value of the pShowTop parameter, the Profit Ratio BAN should adjust in size.

Format Sales and Profit to be $ with 0dp and to display as () when negative, then on all marks cards, add Manufacturer to Detail and Sales, Profit, Profit Ratio and Actual RAG to Tooltip, and adjust Tooltip on all the layers to suit.

Finally remove all gridlines/zero lines/axis ticks and hide the longitude & latitude axis. Hide the null indicator.

Building the Trellis

So now we’ve got the core viz nailed, we need to address the layout, which is to show a gauge for each of the top n manufacturers based on Sales. This means we need to have the gauges indexed/ranked from 1 to n based on total Sales, and then arrange in a grid so that top left is the manufacturer with the highest sales, and bottom right is the manufacturer with the lowest sales. For this we need to assign a row and column number against each manufacturer.

There are multiple blog posts about creating trellis charts. My go to post has always been this one by Chris Love, especially when the requirement is for the trellis to be dynamic (the number of rows/columns can vary) depending on the number of items to be displayed.

However in this instance, using the calculations referenced in the blog doesn’t meet the requirement of ensuring there are more columns than rows. This was an area that got me stumped. As a result, I finished the viz with a version that utilises my ‘go to’ calculations (published here), before then looking at Jack’s solution to get the calculations, which are

Cols Count

//If Rounded value = Value without Round then no remainder, use that number
if SIZE()/round(SQRT(SIZE()),0) = int(SIZE()/round(SQRT(SIZE()),0)) THEN SIZE()/round(SQRT(SIZE()),0)
//Otherwise add 1 to the number of columns
ELSE int(SIZE()/round(SQRT(SIZE()),0)) + 1
END

Size() is reflective of the number of items (in this case Manufacturers) to be displayed… as I write this down, for this specific instance, you could probably replace SIZE() with the parameter pShowTop.

Rowsv2

//For Each Manufacturer, what Row should it be in the Trellis?
//Rank the Manufacturer, Find the Integer portion when dividing by # of Columns
int((INDEX()-1)/ [Cols Count])

Colsv2

//For Each Manufacturer, what Column should it be in the Trellis?
//Rank the Manufacturer, Find the Remainder when dividing by # of Columns
int((INDEX()-1) % [Cols Count])

Make both Rowsv2 and Colsv2 discrete.

Add Rowsv2 to Rows and Colsv2 to Columns. Remove Manufacturer from Columns. It’ll look a bit odd, but be patient.

Edit the Manufacturer pill on the Filter shelf. On the General tab, select None, to remove everything from the filter, then on the Top tab adjust to be based on the top pShowTop by Sales

Now edit the table calculation associated to the Rowsv2 pill, so that it computes by specific dimensions and every field except Point ID is selected. Ensure Manufacturer and Point Segment ID are listed at the top in that order. Set the level to be Manufacturer and apply a custom sort based on Sum of Sales descending

As this is a nested table calculation, select the drop down arrow at the top and apply the same settings to the nested Cols Count field.

Then do exactly the same again for the Colsv2 table calculation settings. If all has been applied successfully, then you should get a grid

where you can then adjust the pShowTop parameter

Hide the Rowsv2 and ColsV2 fields from displaying (uncheck show header). Show the Longitude axis, and fix it to start at 0 but end ‘automatic’. Then hide the axis again (this ensures the arc lands on the row divider).

Then add the viz to a dashboard. My published version based on the trellis using Jack’s calculations is published here.

If you’ve made it to the end – well done! It was a bit of a marathon to do the challenge and another to write this blog. I’m sure it’s been quite an effort to read too, but hopefully you’ve learnt something, and I’ll certainly be referencing this again when a map layer and/or gauge based scenario occurs again.

Happy vizzin’!

Donna