Author: Donna Coles

Can you build a dashboard using containers?

Donna ColesLeave a comment

For this week’s challenge, Kyle got us to look at dashboard layout, specially using containers to arrange the charts and KPIs. He added in a sprinkling of interactivity to make the challenge more complete.

The charts aren’t overly complex, so I won’t go into too much detail on building them all out. I used the latest version of Superstore, v2023.1.

Building the KPIs

When I first did this, I built the KPIs on a single sheet, then realised that wouldn’t work to get the layout required, so I ended up with 3 sheets.

  • Rename Sales to SALES and format to $ with 0 dp.
  • Add Measure Names to Filter and select SALES only.
  • Add Measure Names and Measure Values to the Text shelf.
  • Align centrally and format the text (I used font size 12pt and 20pt).
  • Add Category to the Filter shelf, and select all. Set the filter to apply to worksheets > all using this data source
  • Add Order Date to Filter and select Range of Dates. The Order Date pill will be ‘green’. Click on the context menu of the pill and select the Month (May 2015) option, so the range of dates will change at monthly intervals rather than daily. Set the filter to apply to worksheets > all using this data source
  • Create a field called True containing the value TRUE and False containing the value FALSE and add both these fields to the Detail shelf. These will be needed later to stop the sheet from remaining highlighted on selection.
  • Stop the Tooltip from displaying.
  • Name the sheet Sales

Repeat the steps for the Profit Ratio measure – if this doesn’t exist, create the field

PROFIT RATIO

SUM([Profit])/SUM([Sales])

and format this to % with 1 dp.

For the Orders measure create

ORDERS

COUNTD([Order ID])

If need be, you can simply duplicate the Sales sheet and just change the Measure Names filter to the appropriate measure.

Creating the Line Chart

Create a parameter to store the name of the selected measure.

pSelectedKPI

string parameter defaulted to the word ‘ORDERS’

Create a field to store the measure to display based on the value of the parameter

Measure to Display

CASE [pSelectedKPI]
WHEN ‘ORDERS’ THEN [ORDERS]
WHEN ‘SALES’ THEN SUM([SALES])
WHEN ‘PROFIT RATIO’ THEN [PROFIT RATIO]
END

On a new sheet, add Order Date to Columns and set to be a green (continuous) month level and Measure to Display to Rows.

I wanted to make my tooltips and labels reflect the measure selected (this isn’t actually part of the challenge). I created

Label – Orders

IF [pSelectedKPI] = ‘ORDERS’ THEN [ORDERS] END

format this to a number with 0 dp.

Label – Profit Ratio

IF [pSelectedKPI] = ‘PROFIT RATIO’ THEN [PROFIT RATIO] END

format this to % with 1 dp

Label – Sales

IF [pSelectedKPI] = ‘SALES’ THEN SUM([SALES]) END

format this to $ with 0 dp.

Add all 3 fields to the Tooltip shelf.

Also create

Chart Label

PROPER([pSelectedKPI])

this is a new function introduced in v2023.1 and will convert ‘ORDERS’ to ‘Orders’ and ‘PROFIT RATIO’ to ‘Profit Ratio’ etc. Add this to Tooltip too.

Modify the Tooltip as below – the 3 Label fields should be directly side by side with no spacing. Only one field will have a value at any time.

Remove the titles from the axes, and remove all gridlines. Name the sheet Line.

Building the Bar Chart

Add Category to Rows and Measure to Display to Columns. Sort descending. Add the 3 Label fields to the Label shelf, and arrange side by side.

Add Chart Label to Tooltip and adjust the tooltip.

Hide the axes, remove all gridlines/axis rulers etc and hide the Category label. Adjust the formatting of the fonts as required. Name the sheet Bar.

Building the dashboard

Describing using layout containers can be quite tricky – objects move around as you place things in. I’m going to do my best to describe the set up/structure I have and hope that it gets you what you need.

My preference is to always start with a floating container sized as per the dashboard. I then add tiled objects into that. I also have a habit of trying to rename my containers in the navigation layout pane to help me find the right section.

So let’s start. Create a new dashboard and set it to to 1200 x 900px.

Click the Floating button at the bottom of the left hand pane, and add a Horizontal container. Set the x & y position to be 0,0 and the width to 1200px and height to 900px. Rename the container to Base.

From the Objects list on the Dashboard pane, click Tiled and add a Text object. Enter the text for the dashboard title. Add a blank object beneath the text object.

When working with containers, it’s always good to add blank objects as a ‘starting point’ These all then get removed.

Now add a vertical container between the Title and Blank object. Name this container Main Body. Add a blank object into that container.

Add a vertical container to the left of the blank object in the Main Body container. Name this container Left Nav. Add a Text object into the Left Nav container and enter the instructional text. Add a blank object above the instructional text.

Add another vertical container to the right of the Left Nav container within the Main Body container. Add the Bar and the Line chart into this container, one above the other. Call this container Charts.

At this point a Tiled container object will have been automatically added containing the parameter.

Leave this for now – we’ll address this shortly.

We can remove some of the blanks now too. Remove the blank within the Main Body container, that is to the right of the Charts container. You can select the object on the item hierarchy layout, right click and remove from Dashboard.

You can also remove the blank object at the bottom of the Base container, below the Main Body container. Your item hierarchy should look something like

Now add another Horizontal container to the charts container, above the bar chart. Add the 3 KPI sheets into this container, position side by side. Name the container KPIs. Select the KPIs container, and use the context menu to Distribute Contents Evenly.

If they haven’t already appeared, the select one of the charts/KPIs and from the context menu select Filters > Category and then Filters > Month of Order Date to get the filter controls visible on the dashboard. They should appear on the right hand side, within that Tiled container (underneath the viz).

From the item hierarchy section, expand the Tiled container until you find the controls listed (see how many containers that got automatically added!).

Select the Category filter (easiest to do this by clicking on the object in the item hierarchy), and then move that object and position it above the blank object in the Left Nav container. Then delete the blank object. Select the Month of Order Date filer object and do the same.

Now we’ve got everything we want , we can remove the Tiled container and all of the objects it contains from the dashboard. Just right click on the first Tiled container in the item hierarchy and Remove From Dashboard (say yes/ok to any prompt that appears). You should have something that looks a bit like this – not the item hierarchy layout.

Now you’ve got everything needed on the dashboard in the right containers, we need to tidy it all up.

  • Fix the width of the Left Nav container to around 205 px.
  • Change the Category filter to single value dropdown, defaulted to All
  • Fix the height of the KPIs container to around 175 px
  • Remove the titles from all the KPI objects, and ensure all set to fit entire view
  • Amend the titles of the bar and line charts to reference the Chart Label field.
  • Fix the height of the bar chart to around 340px and set to fit entire view.

  • Set the background colour of the whole dashboard to grey (Format menu -> Dashboard – Dashboard Shading)
  • Set the background colour of the bar and line chart to White and add a grey border (slightly darker than the background colour)
  • Add borders round the 3 KPI charts too.
  • Set padding around all the objects – I tend to use both inner and outer padding. The key is consistency to ensure the spaces between the objects are the same. I typically start with 10 px outer padding all round, and then adjust as required. Sometimes you may add padding to the container and not to the objects themselves, other times you may set the container padding to 0 and apply to the objects, or a combination of both.

Adding the Interactivity

To set the category bar chart to work as a filter, simply select the object and from the context menu select use as filter. Then go to the Actions list (Dashboard > Actions) and edit the ‘Filter 1 (generated)’ action and rename it to something more useful eg Filter bu Category.

For the bar & line chart to update ‘on click’ of a KPI, add a parameter action

Select KPI

On selection of the Orders, Profit Ratio or Sales sheet, set the pSelected KPI parameter, passing the value from the Measure Names field. Keep current value when selection is cleared.

Finally to prevent the KPIs from remaining highlighted in blue on selection, add 3 filter actions (1 per KPI) set up as follows

Deselect Sales KPI

On selection of the Sales sheet on the dashboard, target the Sales sheet directly, and set the fields as Source = True to Target = False.

Finally, to collapse the left hand nav section, select the Left Nav container and from the context menu, select add Show/hide button. A X button will appear which is floating by default. Move this to where you choose (you might need to add some additional left padding to the title to make space.

Load the dashboard in presentation mode to easily test the hide/show functionality.

And hopefully that should be it! Phew!

My published viz is here.

Once you’ve grasped the concept of containers, they really are the best way of controlling the behaviour and layout of objects on your dashboard. When I’m building something formal, I personally never want to have a Tiled container on my dashboard – this is an object that gets automatically added, and you can see from above, how many nested containers it ended up adding through a single action I took. If you’re not careful, you can end up with such a nest of containers, that it can get really hard to unpick.

Happy vizzin’!

Donna

Advertisement

Can You Compare State Sales? Part 1

Donna ColesLeave a comment

This week’s #WOW challenge was set by me and born out of a client requirement to compare store locations via selections from a map. I adapted it to use our favourite Superstore dataset (v2022.4).

The core requirement is to be able to make a selection on the map and see how sales compare to the average of all the other states. There is more to this challenge, but it was too much for one week, so I’ve broken it into 2 parts. This is part 1. In a few weeks time, I’ll be building on this solution for a part 2.

Building the basic hex map

The requirements provide a link to here to get the relevant files needed to complete this challenge and build the hex map – this includes the 2022.4 version of Superstore, the Hex map template, the hexagon shape file and a transparent shape file (more on that one later).

Using the hex map template sheet provided, relate the Orders Superstore data to the hex map sheet, relating State/Province to State.

Then on a new sheet add Column to Column, Row to Row and State to Detail. Edit the Row axis, and reverse the scale.

Change the shape of the mark to be a hexagon (use the provided shape if need be and add to a custom shape palette), and increase the size of the marks. Add Sales to Colour and change to use the Grey sequential colour palette, and adjust the opacity to 80%. Add Country/Region to the Filter shelf and select United States. This will remove Alaska and Hawaii that don’t have any sales and aren’t in the Superstore data set.

Add Abbreviation to the Label shelf and align centrally, Adjust the font size if need be.

Identifying the selected state

We need to be able to capture the state that has been selected ‘on click’. This will be driven by a dashboard action.

When I first built this concept for a client, the natural first step was to utilise sets and set actions; that is capture the selected state in a set, and then colour the map, build the other charts and logic based on the existence in the set. However this method does caused some issues when I tried to prevent the highlighting later, so I chose to use a parameter and parameter actions instead.

Firstly we need a parameter that will be used to capture the state selected ‘on click’

pSelectedState

string parameter defaulted to <empty string>

We can then create

Is Selected State

[State] = [pSelectedState]

As we want to retain the colour by sales on the existing map, we need to make a dual axis. Show the pSelectedState parameter and type in ‘Florida’.

Add another instance of Column to Columns. Remove the Abbreviation label from the second marks card, and replace the Sales field on the Colour shelf with the Is Selected State field. Increase the opacity on this mark to 100% Set the colours as follows :

  • True- teal : #66b3c2
  • False- pale grey : #d3d3d3

Additionally, add Is Selected State to the Shape shelf of the second marks card. Set the True value to use the same hexagon shape, but set the False option to use a transparent shape (a transparent shape file is provided in the g-drive, and needs to be added as a custom shape).

This should make all the other states look like they disappear.

Make the chart dual axis, and synchronise the axis.

Format the Sales to be $ with 0dp, then add to the Tooltip shelf of the second marks card and adjust the tooltip. Remove all gridlines/row & column dividers and hide the axes.

Adding the Interactivity

Create a dashboard sized 1000 x 600.

Add a vertical container and add a text field to create the title. Below the title, add a horizontal container. Add the hexmap sheet into the horizontal container, then add a blank object to the right of it. Remove any legends etc that automatically get added. Set the width of the hexmap object to be fixed to 600 px

and set the height of the horizontal container the hex map sits within to 445px

Show the colour legend and set it to floating, and position bottom left.

Add a parameter action to set the selected state on click

Set Selected State

On selection of the Hexmap sheet, pass the State field into the pSelectedState parameter. Set the value to <empty string> when the selection is cleared.

If we click around on the dashboard page, we can see the colours being set, but everything is fading out on selection. To prevent this, create 2 calculated fields

True

TRUE

False

FALSE

Add these to the Detail shelf of the All Marks card of the hex map sheet. Then back on the dashboard, add a filter dashboard action

Deselect Map Marks

On select of the hex map object on the dashboard, target the hex map sheet itself, setting selected fields such that True = False. Show all values where selection cleared.

Now if we click on the dashboard, the shapes shouldn’t fade into the background. However we can’t ‘unselect’ the state and get back to its original state. This is because the filter action we added to stop the marks from fading actually unselected the mark, so when we click again, we’re not undoing any selection.

To resolve this, we need another calculated field.

State for Param

IF [State] = [pSelectedState] THEN ”
ELSE [State] END

If the state being clicked is the one already captured in the parameter, then set the field to <empty string>, else set the fields to the state being clicked.

Add this to the Detail shelf of the All marks card on the hexmap sheet, and then update the Set Selected State parameter action to pass the State For Param field into the parameter instead of the original State field..

Now when the map is first loaded, the State For Param field contains the name of the state, so that is passed into the parameter on click. As the parameter now has a value, the State For Param field changes for the selected state to be <empty string>, so if the same state is clicked, <empty string> is then passed into the parameter and the view resets.

Building the calculations for the other charts

On a new sheet, add State to Rows, Sales to Text and add Country/Region = United States to Filter.

When a state has been selected, we want to display the state against the selected row, otherwise we want to display the text ‘Other States (Avg)’ .

State Label

IF [Is Selected State] THEN [State] ELSE ‘Other States (Avg)’ END

Now we need to display a different value for the sales measure depending on whether its the selected state or not.

Sales To Display

IF ATTR([Is Selected State]) THEN SUM([Sales])
ELSE SUM([Sales])/COUNTD([State])
END

If the state is the selected one, then use the sales, otherwise average the total sales over the number of states that make up the sales. Format this to $ with 0dp.

Note – in hindsight, this could have just been SUM([Sales])/COUNTD([State]) even for the single state, as since the count of state will be 1, this would just equate to the SUM([Sales]) itself.

Add this to the table, and remove State from the display

Building the bar chart

Duplicate this sheet (as we want to retain the filters), then move Sales To Display to Columns, and add Category to Rows in front of State Label. Add Is Selected State to the Colour shelf.

To ensure the Selected State is always listed first, even if alphabetically it comes after ‘Other States (Avg)’, add Is Selected State to the Rows shelf between Category and State Label. Manually sort it so True is always listed before False, then hide the column (uncheck show header).

Reduce the Size of the bars, remove gridlines and column dividers. Lighten the row dividers. Adjust font sizes. Hide the column labels (hide field labels for rows) and hide the axis.

Remove Sales from the Text shelf, and check Show mark labels instead. Update the Tooltip.

Building the line chart

On a new sheet add Country/Region to Filter and set to United States. Add Order Date to Columns and set to the continuous month level (eg May 2021). Add Sales To Display to Rows, State Label to Detail and Is Selected State to Colour. Manually move the values in the colour legend so that True is listed first. Adjust the tooltip

Remove the axis titles, adjust the axis fonts. Remove row/column dividers and zero lines and axis rulers.

Putting it all together

On the dashboard, add a vertical container between the hexmap and the blank object. Add the line chart and bar chart on top of each other. Remove the title for the bar chart, and update the title of the line chart to reference the pSelectedState parameter.

Remove the blank object to the right of the bar/line charts.

We need to control when the bar and line charts display, so we’ll use dynamic zone visibility for this, and for this we need another boolean field

Show Viz

{FIXED: MAX(IF [Is Selected State] THEN TRUE ELSE FALSE END)}

Is Selected State is a boolean field which essentially is 1 for True and 0 for False. If there is a state selected, the maximum value across all the data records, will be 1, so the field returns true, otherwise its 0, so false.

Use this to control visibility using value for the line and bar chart objects.

Make any further adjustments to the layout required -the size of the hex shapes may need tweaking for example. Then interact with the viz to check all is working as expected.

My published viz is here.

Happy vizzin’!

Donna

Let’s practice using the Device Designer

Donna Coles1 Comment

Inspired by a discussion on Twitter that resulted in this blog post from Kevin Flerlage, Sean challenged us this week to build a viz that should adapt for different devices.

This makes use of the dashboard layout feature in Tableau (see here for further information). The focus on this week is really on the layouts, the charts are relatively straightforward, so I’m just going to summarise each one fairly quickly.

Note on the data

In the requirements, Sean talked about filtering the date to 2023 and assuming a YTD value where ‘today’ was 8th March 2023. The data set linked in the requirements didn’t contain that information. I chose instead just to use the 2022.4 version of Superstore I had. As this challenge wasn’t going to contain any complicated table calcs/LODs I wasn’t worried that my numbers might not match.

Filtering the data

To restrict the information based on Sean’s requirements, I created a parameter to represent ‘today’

pToday

date parameter defaulted to 8 March 2022

I then created a field to use to ensure I only counted data up to that date.

Dates to Include

[Order Date] <= [pToday]

I added this to the Filter shelf and set to True. In addition I added Order Date to the Filter shelf, and selected Years > 2022. Both these fields I set to apply to all worksheets using this datasource.

Building the KPIs

Format Sales and Profit to be $ with 0 dp. If it doesn’t exist, create

Profit Ratio

SUM([Profit])/SUM([Sales])

and format this to % to 1 dp.

Add Measure Names to Filter and filter to the three measures (Sales, Profit and Profit Ratio). Add Measure Names to Columns and Measure Values to Text. Re-order as required. Add Measure Names to Text too and format the Text as required, Remove row dividers and hide the column headers.

Building the bar chart

Add Sub-Category to Rows and Profit Ratio to Columns and sort descending, Add Sales to Colour and add Profit to Tooltip. Adjust tooltip. Hide the Sub-Category column heading from displaying (right click > Hide field labels for Rows). Change the title of the viz.

Building the Map

Double click State/Province to automatically generate a map of the USA (if it doesn’t display, check the location is set to USA – Map menu > Edit Locations).

Add Profit Ratio to Colour and add Sales and Profit to Tooltip. Adjust tooltip.

Remove all the background map layers by selecting Background Layers from the Map menu, and unchecking all the options listed on the menu that displays on the left hand side.

Hide the ‘unknown’ indicator if its displaying (right click > hide indicator).

Stop the map options (that allow zoom & pan etc) from displaying by selecting Map Options from the Map menu and unchecking all the options that are presented.

Remove row and column dividers and change the title of the viz.

Building the Scatter Plot

Add Sales to Columns and Profit to Rows. Add Order ID to Detail and Profit Ratio to Colour. Make the zero lines slightly more prominent and title the viz.

On a new sheet, add Customer Name and Product Name to Rows. Add Measure Names to Columns and Measure Values to Text. Add Measure Names to Filter and restrict to Quantity, Sales and Profit. Reorder the measures as required.

Add subtotals -from the Analysis menu select Totals > Add all Subtotals.

Set the row banding to none. Centre align the Customer Name column. Name the sheet Order Details or similar

Back on the scatter plot sheet, adjust the Tooltip and add a reference to the Order Details sheet by using the Insert > Sheets > select sheet

How well the viz in tooltip displays can often be a bit of trial and error. You may need to adjust the width and height properties on the referenced sheet in the tooltip. Also, sometimes setting the Fit property of the Order Details sheet may help too.

Building the default dashboard

Create a dashboard sized Generic Desktop. Set the background of the dashboard to grey (Format menu > Dashboard) Arrange the four vizzes on the dashboard. You may need to use horizontal and vertical containers to help you align everything where you want. Add padding around all the viz objects (I set mine to 10). Hide the title of the KPI viz. For the other 3 vizzes, set the background to white, so the title is also in white.

Set filter actions on both the bar chart and map, by selecting the object and then choosing Use as Filter from the context menu.

Click on the bar and check the other vizzes all filter. Do the same with the map. Now we’re ready to apply the different device layouts.

Creating the Tablet layout

The dashboard initially created is the Default dashboard, and we can tell this by looking at the left hand pane.

To add a new layout for a generic tablet, click the Device Preview button.

A Device Preview bar will appear at the top of the dashboard, Scroll through the Device type options until Tablet is displayed. By default the model should already be Generic Tablet. A border will display over the dashboard which indicates the boundaries of the dashboard based on the dimensions of the device. Click the Add Tablet Layout button.

The dashboard will immediately be resized to fit the boundary. Some of the vizzes are squashed up. Manually readjust so everything is displayed as required. A Tablet option will have appeared on the left hand side, and you can now toggle between Default and Tablet (click on the words in the left hand pane) and see the dashboard adjusting.

Creating the phone layout

Click on the Phone option (top left under Tablet), or scroll through and select Device Type = Phone.

By default, everything on the default dashboard is displayed on the Phone layout, which is obviously a portrait layout, optimised for scrolling down.

We don’t want the title, or the Scatter plot on this layout. To remove them, click the locked padlock icon and it will change to an unlocked icon, and the dashboard will be editable..

Remove the title object and the scatter plot object. In my case I also removed my footer information. Adjust the bar chart so its a bit longer, so the bars and labels are readable. It doesn’t matter that it will extend outside the bounds.

And that’s it – you now have 3 layouts for a single dashboard. Publish the viz to Tableau Public, then test out by accessing the viz from a tablet and/or your phone. Tableau will detect the type of device you’re accessing from, and show the most suitable display. Below are images of my viz accessed from my laptop, and on my mobile.

My published viz is accessible here.

Happy vizzin’!

Donna

A tricky filter

Donna Coles5 Comments

In this week’s challenge, Erica set us the task of building a filter that only contained a subset of the dimension values – ie a set of core values always had to remain in the view, and weren’t available to be filtered out.

Erica advised there were hints available, and that she had solved the problem herself via an existing Tableau Knowledge Base article.

The requirements stated Sets were involved and so I attempted down this path, creating a set to store the ‘core’ cities (as per the requirements), and then using a combined set of all cities and ‘core’ cities to just display the values not in ‘core’. However I couldn’t get things working, so I checked out the hints.

The first hint alluded to 2 sheets, which initially I thought one for the ‘core’ cities and one for the rest, but quickly realised this would only work if there hadn’t been the additional ‘bonus’ requirement to sort the data based on the sales (ie the core cities and the rest could become interspersed in the viz).

So after further fiddling, and unsuccessful ideas, I ended up referencing the KB article and built out a solution. After publishing, my good friend and fellow #WOW participant Rosario Gauna, published her solution which she managed in a single sheet, and in a manner that was much more elegant. So it’s a double solution guide today – what I did based on the KB article and a recreation of Rosario’s solution (so I have this to reference and remind myself if ever I have the need to recreate).

Solution 1 – The 2 sheet solution

Firstly, create a set called Key Cities (right click on City > Create > Set) and select the 5 cities listed in the requirements.

Key Cities

On a new sheet, add State/Province to the Filter shelf and choose Ohio, then add City to Rows and Sales to Columns and sort descending. Add Key Cities to Colour and adjust accordingly.

Call this sheet Sales by City

On a new sheet, add State/Province to Filter and select Ohio, then add City to Rows. Call this sheet Filter Sheet.

Duplicate the City field (right click field in the data pane and select Duplicate). This will create a new field called City (copy) in the data pane.

Add City (copy) to the Filter shelf of the Filter Sheet sheet, select the 5 core cities and then check the Exclude checkbox.

Add the original City field to the Filter shelf as well and select All. Show the filter on the sheet, and adjust so it displays Only Relevant Values

The list of options in the filter list should only show the cities in Ohio that aren’t one of the five key states.

Set the City filter to Apply to Worksheets > Selected worksheets and select the Sales by City worksheet

Customise the City filter in the Filter Sheet sheet so that the All option does not display. From the context menu of the City filter control, select Customise and ensure Show ‘All’ Value is unchecked.

Navigate back to the Sales by City sheet and show the City filter values, ensuring all are displayed. This list will include the key cities, but don’t worry. Uncheck a value that isn’t in the list of key cities eg Bowling Green. The city will disappear from the viz, but if you navigate to the Filter Sheet, you should also see the value is unselected in that list too.

This is the filtering behaviour we’re after – selections made to the City filter on the Filter Sheet affect the values in the City filter on the Sales by City sheet.

Now we need to address the sorting.

Again I think I ended up doing something a bit more complicated than needed – check out the sorting described in the 2nd solution, as that would apply here too – it just isn’t what I did at the time.

Firstly, we need a parameter to determine which sort selection to use

pSort

string parameter containing two list entries Key Cities and Sales, defaulted to Key Cities

I decided I wanted to sort by a number, which for Sales was fine, but when Key Cities was selected, I needed to ensure the values for the Key Cities were always greater than the maximum value for the non key cities. For this I needed to get a handle on the value of sales for the non key city that had the largest sales.

Max Non Key Sales

{FIXED : MAX(IF NOT [Key Cities] THEN ({FIXED [State/Province], [City] : SUM ([Sales])})END)}

If the city is not a key city, then get the total sales for each State & City (potential that a city can exist in multiple states, hence the need to declare the State), and then return the max of those.

To see what this is doing, on a new sheet, add State/Province to Filter and select Ohio, then add Key Cities and City to Rows and Sales to Text and sort by Sales descending

We’re looking for the value 8203 as this is the largest sales for the cities not tagged as a Key City.

Add Max Sales Non Key City to the view…. the value doesn’t match what we expected.

This is because a FIXED level of detail (LOD) calculation works across the entire data set, so the fact we’ve filtered by Ohio is being disregarded. To resolve this, set the State/Province field on the Filter shelf so it is Add To Context

This pill will change to grey, and the values should update, as now the LOD is being applied after the context filter has been applied.

With this we can work out a sort field

Sort

CASE [pSort]
WHEN ‘Sales’ THEN SUM([Sales]) * -1
ELSE
(IF ATTR([Key Cities]) THEN SUM([Sales]) + SUM([Max Sales Non Key]) ELSE SUM([Sales]) END) * -1

END

If we’re sorting by Sales then use the total Sales value * -1, otherwise, if we’re sorting by Key Cities then, if the City is a key city, then add the total sales to the max sales value, otherwise just use the total sales value. Multiple the result by -1. By adding this value, it ensures the values for the Key Cities are always larger than those for the non key cities. The -1 means the sort will be descending.

Test this out, by adding the Sort field as a discrete (blue) field to the Rows of the test sheet we’ve been using above. Ensure the Sort field is listed first, and move the Key Cities field to be third. Show the parameter control, and test switching between the options. The values in the Sort field are always in an ascending order, but the displayed Sales values will be ordered depending on the sort option chosen

Back on the Sales by City sheet, add the Sort pill to the Rows before the City pill, and add the State/Province filter to context.

Hide the Sort field.

The labels need to be displayed inside the bars, so for this we need a dual axis.

Add another copy of Sales to the Columns. On the second Sales marks card, set the option to Show mark labels from the Label shelf. We need the text of the label to be different to the existing bars, so create a duplicate of the Key Cities field, so we have Key Cities (copy) and add this to the Colour shelf of the second marks card. Adjust the colours accordingly to white and black.

Change the mark type of the 2nd marks card to Gantt bar, reduce the opacity of the Colour to 0% and reduce the Size to as small as possible. Adjust the alignment of the Label to left middle, and set the font to be bold and match mark colour.

Make the chart dual axis and synchronise the axis. Set the mark type of the first marks card back to a bar if it changes.

Remove all row and column dividers, and hide the top axis. Hide the City column label too. Edit the bottom axis, and fix to start from 0 and end automatic. Adjust the tick marks to display every 5000 values.

Add this sheet to a dashboard. Remove the colour legends that automatically get added and remove the City filter control too. Leave the sort parameter.

Then add the Filter Sheet as a Floating object and position bottom right. The City filter for this sheet should also automatically display. If it doesn’t show it (click the context menu of the Filter Sheet object > Filters > City).

Change the City filter to be a multiple values dropdown control and set it to be fixed (unselect the Floating option on the context menu).

Now hide the title on the Filter Sheet object and resize to make it teeny tiny, so you can’t see anything

Now you have the core objects needed for a functional dashboard – you’ll just want to take some time moving them into place, and excluding other Cities.

My workbook that matches this solution is here.

Solution 2 – the 1 sheet solution

So shout out again to Rosario Gauna, as this is actually her solution!

We’ll build this out in a table first, so we can see what’s going on.

On a new sheet add State/Province to Filter and select Ohio, then add City and Key Cities (the set created above) to Rows. Add Sales to Text.

What we’re going to do is create another set which will just contain the cities not identified as key cities. For this, we need to store against every row (including the key cities) the name of a City that isn’t a key city. For those that already aren’t a key city, that is just its own City name, but for those that are key cities, we want to store a non key city…. sounds confusing right…. let’s build this up.

Firstly, let’s just get those non key cities

Non Key City

IF NOT [Key Cities] THEN [City] END

Add this to the sheet. It shows NULL against all the Key Cities and the City value for all the others

We’re going to use this to set a ‘default; value against the key cities.

Min Non Key City

{FIXED: MIN([Non Key City ])}

Theis returns the value from the Non Key City field which is alphabetically first. Using MAX would work just as well. When we add this to the sheet, we also need to set the State/Province filter to be Add to Context, otherwise we get a City from the whole data set, and not just Ohio.

We can now create a field that will just contain a distinct list of the non key cities

Other Cities

IF NOT [Key Cities] THEN [City] ELSE [Min Non Key City] END

Add this to the sheet

For every City, the Other Cities field contains a non key city value. Now we have this, we can create a set from this field

Other Cities Set

Ensure all values are selected – don’t worry that you can see cities that aren’t relevant at this stage

Add Other Cities Set to the Filter shelf and also add to the Rows shelf next to the Key Cities field. From the context menu of the Other Cities Set on the Filter shelf, select Show Set. The list of non key cities should be displayed. If there’s more than you expect, ensure the control is set to All Values in Context.

If you uncheck Bowling Green from the list, all the key cities and Bowling Green will disappear, but we don’t want this. We only want the row where City=Bowling Green to disappear. For this we need

Records to Keep

[Key Cities] OR [Other Cities Set]

Add this to the Filter shelf and set to True. Remove the Other Cities Set from the Filter shelf (the list should remain). Now if you remove Bowling Green, only that row should disappear, and if you uncheck all, so no city is selected, the key cities should remain

Adjust the display so the rows are sorted by Sales descending (either use the Sort button in the toolbar, or set the sort on the City field).

Then create a field

Sort Order

CASE [pSort]
WHEN ‘Key Cities’ THEN [Key Cities]
ELSE TRUE
END

Add this to the Rows in front of City, and show the pSort parameter (see above for details if you haven’t created this yet). When pSort is set to Key Cities, manually change the order of the values displayed so True is listed before False. The rows are displayed in descending Sales value for each Sort Order Value. When Sales is selected to sort by, the Sort Order is true for all rows.

Now we’ve got all the components needed to build the viz, and you should be able to adapt the steps above to get it to work. The key difference is using the Records To Keep field on the Filter shelf, displaying the members of the Other Cities Set to control the filtering, and managing the sort using the Sort Order field instead.

My workbook showing this solution is published here.

Happy vizzin’!

Donna

Can you build a comet chart as an alternative to a side by side bar chart?

Donna Coles1 Comment

In the final week of ‘alternative charts month’, Luke set this challenge as different way of presenting data that you might typically see in a side-by-side bar chart.

Luke had indicated on the #WOW splash screen, that this challenge was ‘easy’, but that’s always dependent on your level of Tableau. He also added a note in the requirements that if you wanted to be ‘advanced’ to solve it with Table Calcs only.

I figured I’d just start and see what I ended up with (sometimes, my natural brain thinking takes me down a table calc route..)

In a change to my usual starting point, I started trying to remember what I needed to do to get the comet… I felt pretty sure that path would be involved somewhere.

So, I added Order Date to the Filter shelf and filtered to years 2021 and 2022 only.

Then I added Sub-Category to Rows, Sales to Columns and Order Date (which defaulted to YEAR(Order Date)) to Detail. I changed the mark type to circle initially.

Ok – I had what I was expecting – 2 circles per row, one for each year.

So then I change the mark type to line and moved YEAR([Order Date]) from Detail to Path. This meant my lines were joined.

I then added Order Date to Size, and reset Order Date to be at the YEAR level. Hey presto! My comet shapes appeared.

I now wanted to show a white circle mark just for the 2022 sales, so I created

Max Year Sales LOD

{FIXED [Sub-Category]:SUM(IF YEAR([Order Date]) = {FIXED:MAX(YEAR([Order Date]))} THEN [Sales] END)}

this looks a bit long-winded ( I do usually break this up)… so let’s review what’s going on…

{FIXED:MAX(YEAR([Order Date]))} returns the latest year in the data set (ie 2022) and spreads that across every row of data. So the formula is comparing each row, and if the Order Date year matches 2022, the value of the Sales is returned. This is then all aggregated and totalled for each Sub-Category.

Add this field to Columns, make dual axis and synchronise axis.

Remove Measure Names from the All marks card and change the mark type of the Max Years Sales LOD card to circle. Colour white.

Remove the YEAR([Order Date]) pill from the Size shelf of the Max Years Sales LOD card, so the size of the comet (the Sales card) and the circle can be adjusted independently. Adjust the sizes enough so the comet is visible around the circle.

Sort the Sub-Category field by Max Year Sales LOD descending

Next we need to colour the comets based on whether Sales increased or decreased.

Prev Year Sales LOD

{FIXED [Sub-Category]: SUM( IF YEAR([Order Date]) = {FIXED:MAX(YEAR([Order Date]))} -1 THEN [Sales] END)}

is doing similar to the above calculation, but {FIXED:MAX(YEAR([Order Date]))} -1 returns 2021 instead.

and with this we can created

Sales Increased? LOD

[Max Year Sales LOD] > [Prev Year Sales LOD]

Add this to the Colour shelf of the Sales marks card, and adjust accordingly.

To label the comets, check the show mark labels checkbox on the Label shelf dialog, and set to line ends and label end of line. You may need to check the allow labels to overlap option too if you’re not seeing all the labels.

The dashboard shows a circular size legend which is related to the circle mark, so I created

Order Date (Years)

YEAR([Order Date])

and added this to the Size shelf of the Max Year Sales LOD marks card.

Add Sales to the Tooltip shelf of the Max Year Sales LOD marks card too and adjust the tooltips.

Add row dividers, and remove all column dividers, gridlines and axis. Adjust the formatting of the Sub-Category row labels and hide the column title. Set the background of the worksheet to a grey colour.

And so that ended up being the LOD version of the chart, which is accessible from here.

But I had time, so I figured I’d see if I could crack the Table calcs version…

Building the Table Calculation Solution

This starts by repeating the intial steps above to get a basic single axis comet chart for Sales, split by Year.

We now need to get the sales for 2022 only. For starters, let’s identify the latest year

Latest Year

WINDOW_MAX(MAX(YEAR([Order Date])))

and let’s build up a table, so we can start to sense check what’s going on, as table calcs can be pesky!

Our Latest Year table calc is returning 2022 for every row in our table. To get the sales just for 2022

Window Max Year Sales

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

if the order date year is 2022, then return Sales (otherwise null) and spread the maximum value across the rows. When we add this into the table, we need to set the table calculation to compute using Year or Order Date, so that it is calculating the WINDOW_MAX for each Sub-Category

Add this field to Columns on the comet chart, and adjust the table calculation so Window Max Year Sales is computing by Year of Order Date only, and Latest Year by both fields (see the Nested Calculations dropdown)

Make the chart dual axis, and synchronise the axis. Make the adjustments to the mark types and sizes as described above.

We can’t sort the Sub-Category field in the way we did above, as table calculation fields aren’t accessible in the sort dialog. Instead add Window Max Year Sales to Rows and change it to be discrete (blue pill) and move it to be in front of Sub_Category. Adjust the table calc settings to match that described above. This should make the chart sort ascending.

To reverse, double click into the blue Window Max Year Sales pill on Rows and add * -1 to the end

Annoyingly this will revert it back to a measure, so reapply the steps above, and you should end up with a correctly sorted display. Hide the Window Max Year Sales blue pill.

Now to colour the comet.

Back to the tabular view. Add a Difference quick table calculation to the Sales pill and edit the table calculation to compute using Year(Order Date) only.

Drag the Sales pill with the difference table calc from the Measure Values section and drop into the left hand data pane. This will create a dedicated instance of the calculation. Rename it to

Sales Diff – TC

ZN(SUM([Sales])) – LOOKUP(ZN(SUM([Sales])), -1)

If you examine it, it should contain the above calculation.

With this we can then work out if sales have increased or not

Sales Increased? TC

WINDOW_MAX(IF [Sales Diff – TC] > 0 THEN 1 ELSE 0 END)

Adding this into the table, and setting the nested table calcs to both compute by Year Order Date, you can see that the values for each Sub-Category are either 1 or 0.

Add this to the Colour shelf of the Sales marks card. Make sure the field is discrete and the table calcs are set to compute by Year Order Date. Adjust the colours.

Finally make adjustments for the tooltip and adjust the formatting to clean up the chart. My table calc version of the viz is here.

Happy vizzin’!

Donna

Can you create a jittered bar chart?

Donna Coles1 Comment

The theme of alternative chart types continued with Lorna setting this challenge to create a jittered bar chart. We needed to use the same fake survey data set used in week 4, so initially some data remodelling was required. I’ve already blogged that here, so please refer to the modelling the data section if you need help.

The questions all need to be grouped based on the text that preceded the question, so I created

Question Group

TRIM( SPLIT( [Question], “-“, 1 ) )

which split off the text in front of the first ‘-‘. I actually created this by right-clicking on the Question field > Transform > Custom Split and selecting to to split off the first 1 column using the – separator.

I also created a field to number the responses

Response No

CASE [Response]
WHEN ‘Strongly Disagree’ THEN 1
WHEN ‘Disagree’ THEN 2
WHEN ‘Neutral’ THEn 3
WHEN ‘Agree’ THEN 4
ELSE 5
END

Once I’d done that I was able to ratify the numbers by building a simple table with Response No and Response on Columns, Question Group on Rows, and CNT(Id) on Text.

Eyeballing these numbers against where the ‘bars’ in Lorna’s viz stopped and I figured that was on the right track. However then I wasn’t particularly sure what to do next.

Lorna’s instructions were very brief. I figured I’d need to use a jitter (ie RANDOM()) somewhere, and as I’d need to add Id to the Detail shelf at some point, to generate 1 dot per respondent, I deduced I’d also need

Total Respondents

{FIXED [Response], [Question Group]:COUNT([Id])}

Adding this into by table above and I got the same values as CNT(Id) which is what I expected.

After scratching my head a bit, I decided that Lorna was possibly being vague, as there was probably help ‘out there’. So I googled, and immediately stumbled upon this very useful blog post, which pointed me in the right direction for the additional calculations required.

I created

Columns

RANDOM()

Jitter

RANDOM()

note – due to the way RANDOM() works, Jitter and Columns won’t contain the same value.

Rows

[Jitter] * [Total Respondents]

And with these I could build out the viz

Add Response No, Response and Columns to Columns. Change Columns to be a dimension.

Add Question Group and Rows to Rows. Change Rows to be a dimension. Add Id to Detail.

Change the mark type to circle, reduce the size and add Question Group to Colour and adjust accordingly.

Finally add Question to Tooltip and adjust the tooltip. Then remove all gridlines, hide the Columns axis, change the title of the Rows axis, and remove the row and column titles (hide field labels for rows / columns).

A pretty short blog today! My published viz is here.

Happy vizzin’!

Donna

Can you create a normalised jitter plot?

Donna Coles1 Comment

Continuing the theme of alternative chart types, Kyle decided to challenge us to recreate this jitter plot inspired by an example from The Big Book of Dashboards.

I’ve built jitter plots in the past for #WorkoutWednesday challenges (the hidden RANDOM() function is your friend in this), but I wanted to see how far I could get without having to peak at my previous solutions.

So I connected to the baseball data provided, and cracked on, building the jitter in a single sheet using Measure Names on the columns. But then I got stuck when it came to labelling the tooltips…. surely this didn’t need a sheet per measure did it…

…maybe it did… so I proceeded to recreate as 4 separate sheets, and felt quite smug that I’d managed to make use of the ‘little used’ worksheet caption to provide the summary detail at the bottom of each measure. When I’d finished, I checked Kyle’s solution, as the summary values for the SLG & OPS measures seemed to be mixed up…. and what did I find…. he had managed to build the jitter within a single sheet as he had pivoted the data first! Argggghhhh! It just hadn’t crossed my mind, and Kyle had chosen not to drop that hint in the requirements…. hey ho! c’est la vie! I may well recreate with a pivoted version at a later date, but for now, I am blogging what I did…

My solution has ended up with a lot of calculated fields as a result, as equivalent fields needed to be created for every measure. Most of this was managed via duplicating and editing existing fields, so it actually wasn’t too onerous.

Building the calculated fields

We’ll start as usual by building out the fields required, and will focus on the BA measure initially.

Add Name and BA into a tabular view and Sort descending. Format the BA measure to be a number with 3 decimal places.

We need to know the rank of each player. Create a calculated field

Rank BA

RANK(SUM([BA]))

Add this to the view, and we should get the player rankings displayed from 1 downwards.

Now the requirement wants us to normalise the measures so they can be displayed on the same axis (or in my case, since it’s not a single chart I’m building), within the same axis range.

What this means is we want to plot the measure on a scale between 0 and 1 where 0 represents the lowest measure value, and 1 the highest. for this we need

Min BA

{FIXED :MIN([BA])}

and

Max BA

{FIXED :MAX([BA])}

The normalised value then becomes

Normalise BA

([BA] – [Min BA])/([Max BA]-[Min BA])

The difference between the current value and the lowest value, as a proportion of the range (ie the difference between the highest and lowest values).

Adding this to the table, you should see that the Normalise BA value for the highest ranked player is 1 and that for the lowest ranked is 0.

As part of the information displayed, we also need to know the percentile each player is in.

Percentile BA

RANK_PERCENTILE(SUM([BA]))

Format this to a percentage with 0 dp and add into the table.

Next we need to identify the player selected, so we’re going to create a parameter based off of the Name.

Select a Player

Right click Name -> create -> Parameter. This will open the parameter dialog and auto populate the list of options with the values from the Name field. Default the parameter to Julio Rodriguez.

We can then create a field to identify if the player is the one selected

Is Selected Player?

[Name] = [Select a Player]

Add this into the table, on the Rows before Name and sort so True is listed at the top (just easier to check the results).

So now we need to identify the rank and percentile of the selected player only

Selected Player BA Rank

WINDOW_MAX(IF ATTR([Is Selected Player]) THEN [Rank BA] END)

format this to a number with 0 dp

Selected Player BA Percentile

WINDOW_MAX(IF ATTR([Is Selected Player]) THEN [Percentile BA] END)

format this to a percentage with 0 dp.

The window_max function has the effect of ‘spreading’ the result over all the rows.

Finally we need to get a count of all the players

Count Players

{FIXED:COUNTD([Name])}

format this to a number with 0 dp.

Building the Jitter Plot

To build a jitter plot, we need to plot each mark against 2 axes. The Normalise BA measure is one axis, but we need to create ‘something’ for the other. This is the value to make the ‘jitter’ which is essentially an arbitrary value between 0 and 1 that we can plot the mark against, and means the marks don’t all end up in a single line on top of each other, and we can get a better ‘feel’ for the volume of data being represented.

Jitter

RANDOM()

The random() function is a ‘hidden’ function that will, as it’s name suggests, generate a random number. It is ‘hidden’ as it only works with some data sources. Excel for example is fine, but if you were connected to a Snowflake database, you can’t use it.

The nature of random, also means that you can’t guarantee the value it produces, and it will regenerate on data refresh, so if you’re looking to compare your solution directly, your dots will not be positioned exactly the same.

On a new sheet add Jitter to Columns and Normalise BA to Rows. Add Name to Detail and change the mark type to Circle.

Add Is Selected Player to Colour, adjust accordingly and add a border to the circle. I dropped the opacity to 70%. Order the colour legend, so True is listed first, to ensure this circle is always ‘on top’.

Then add Is Selected Player to Size. Edit the sizes so they are reversed and adjust the sizes until you’re happy.

To label just the selected player mark

Label:BA

IF [Is Selected Player] THEN [BA] END

format this with a custom number font ,##.000;-#,##.000

Add this to the Label shelf and adjust the font colour, and align centrally

Add Rank BA and BA to the Tooltip shelf and adjust tooltip to suit. You will need to adjust the table calculation setting of the Rank BA field so that it is computing by all the fields.

Add Selected Player BA Rank and Selected Player BA Percentile and Count Players to the Detail shelf. Adjust the table calculations as above (including any nested calcs), then show the worksheet caption (Worksheet -> Show Caption), and edit the caption to display the relevant text.

From the analytics pane, drag the Median with Quartiles option onto the canvas and drop it on the table / Normalise BA axis option. Remove the quartile reference lines (right cick axis -> remove reference line), and edit the median reference line to be a dashed line with no label.

Finally remove all gridlines/dividers/axes lines and hide the axes. Title the sheet as per the measure ie BA, and align centrally.

Format the Caption and the Title to have a light grey background and a slightly darker thin border.

Now, repeat all that for the other measures 🙂 This isn’t that bad. All the fields above labelled BA, need duplicating, renaming and updated to reference the next measure eg OBP.

Once done, duplicate the BA jitter plot sheet, and replace all the ‘BA’ related fields with the equivalent ones, by dragging the equivalent field and dropping it directly on top. Sense check the table calculation settings are all ok. You may need to update the text in the caption, as that seems to lose anything to do with the table calculation fields referenced when they get touched.

Ultimately you should end up with 4 sheets.

Putting it all together

On a dashboard, use a horizontal container to position all 4 sheets in side by side. Show the worksheet caption for each sheet. Reduce the outer padding for each sheet to 0, and add a thin border around each sheet.

Add a parameter action to drive the interactivity ‘on click’ of a circle

Select Player

On select of any of the source sheets, update the Select a Player parameter with the value from the Name field. Retain the selected value on ‘unclick’

To prevent the selection on click from being ‘highlighted’, and al the other marks ‘fading’, we need one final step.

Create new calculated fields

True

TRUE

False

FALSE

Add both these fields to the Detail shelf of each of the 4 sheets.

Then add a dashboard filter action for each sheet which on select, goes from the sheet on the dashboard to the worksheet itself, passing the selected fields of True = False. Show all values when unselected.

My published viz is here. Kyle’s solution with a lot less calculated fields, and only 2 sheets (1 for the jitter and 1 for the summary section at the bottom) is here. You will need to pivot the data via the data source pane first through 🙂 Next time, when I really feel something should be able to be done in 1 sheet, I’ll try to think a little longer…. upshot though, I impressed myself at the use of the caption for the summary – something I must consider using more often!

Happy vizzin’!

Donna

Can you build a funnel chart?

Donna Coles1 Comment

For my first #WOW2023 challenge, as an ‘official’ part of the #WOW crew, I challenged everyone to build this funnel chart. Inspiration came from a colleague who posed me the challenge of building something similar, ensuring the bars were a single entity when clicked on. In that example we’d just used Superstore data, but it wasn’t really reflective of how a funnel would typically work, so I sourced some dummy sales opportunity data and made the challenge more relevant to a real business scenario.

Building the Funnel Chart

I saw this as a table calculation exercise, though I have subsequently seen solutions that manage it all through LODs. I’ll obviously blog my solution.

Let’s start by getting the core data into a table, so we can see what we’re aiming for

The total value of all the Opportunities is $91.4M. Based on the sales pipeline process and the assumptions documented in the requirements, all of that value will have passed through the first stage – Prospecting.

There is $6.1M still sitting in Prospecting, so when we move onto the next stage, stage 2 -Qualification, the total amount that has passed through will be $91.4M – $6.1M = $85.3M

As there is $6.5M sitting in Qualification, as we move to stage 3, the total amount that has passed through will be $85.3M – $6.5M = $78.7M. A similar pattern repeats for Stage 4, but for Stage 5, Closed Won, the value that has ‘passed’ through that stage, is equivalent to itself.

If we amend the Value field to be a Running Total quick table calculation (and add Value back into the view too), we get

The Running Sum is computing down the table , with the value in each row being the sum of the preceding rows. But this is opposite to what we want. We want Stage 1 to have the biggest value, the total, and the values to get smaller as you look down the table.

To resolve this, edit the table calculation and change the Sort order to custom sort by Minimum Stage No Descending

This results in the running total to sum from the last row (Stage 6) up to the first row (Stage 1), and you can see the value against Stage 1 matches the total for the Value column.

However, this still isn’t quite what we need. While we need to consider the amount for Closed Lost in stages 1-4, we shouldn’t be including it in stage 5, Closed Won.

While excluding the Stage 6 from the view (Stage No on Filter shelf set to Exclude 6) , gives the correct value for Stage 5, we’ve now lost the amount for Closed Lost in the other rows

So, we need some additional calculations to help resolve this.

Amount Lost

{FIXED : SUM(IF [Stage No]=6 THEN [Value] END)}

This just captures the amount of Stage 6 and ‘spreads it across every row of data.

Let’s now get our running total field, baked into it’s own calculation, as this will help with other calcs

Cumulative Value Per Stage

RUNNING_SUM(SUM([Value]))

Replace the Value table calc field with this one, ensuring the table calculation settings are identical to those described above.

Now let’s add the Amount Lost onto the Cumulative Value Per Stage unless we’re at Stage 5, Closed Won

Total Amount Per Stage Inc Lost

IF MIN([Stage No]) = 5 THEN [Cumulative Value Per Stage]
ELSE [Cumulative Value Per Stage] + SUM([Amount Lost]) END

Pop this into the table (checking the table calc settings are as expected) and the total amount now stored against Stage 1, is the total value of all the Opportunities.

Ok, so now we know the values, how do we plot this in such a way that it represents a funnel. I chose to do this by first working out the proportion of the total value that the ‘reverse’ cumulative value represented.

Total Value

{FIXED:SUM([Value])}

meant I could determine

Proportion of Total

[Total Amount Per Stage Inc Lost] / SUM([Total Value])

I formatted this to percentage with 0 dp.

Adding these into the table

If we plotted this information on a bar chart, we’d get this

but we need to ‘centre’ each bar in relation to the first bar to give us the funnel effect. This means we need to shift each ‘bar’ to the right, by half the amount that is the difference between 100% and the Proportion of Total.

Position to Plot

(1 – [Proportion of Total])/2

We can’t represent this with a actual bar chart, but instead we can use a Gannt chart.

  • Stage No to Rows
  • Stage No to Filter and exclude Stage 6
  • Stage to Detail
  • Position to Plot to Columns, adjusting the table calculation as previously described
  • Change mark type to Gantt bar
  • Add Proportion of Total to Size (and verify the table calc is set properly)

Hey presto! A funnel!

To finalise

  • add Stage to Label and align centrally. Make the font bold and match mark colour.
  • add Cumulative Value per Stage to Colour and reverse the colour range (via the Edit Colours dialog)
  • Widen each row a bit.
  • Fix the Position to Plot axis to start at 0 and end at 1 to ensure the funnel is centred exactly.
  • Add Value and Total Amount Per Stage Inc Lost to the Tooltip and adjust the tooltip text accordingly (don’t forget to sense check the table calc settings!).
  • Hide the Position to Plot axis, and the Stage No column. Remove all gridlines, zero lines, axis rulers /row/column banding etc.

Building the KPIs

We need a few calculated fields to store the required numbers

Won

{FIXED:SUM(IF [Stage No]=5 THEN [Value] END)} / [Total Value]

formatted to a percentage with 0 dp.

Lost

{FIXED:SUM(IF [Stage No]=6 THEN [Value] END)} / [Total Value]

formatted to a percentage with 0 dp.

Outstanding

1-([Lost] + [Won])

formatted to a percentage with 0dp.

On a new sheet add Measure Names to Columns and Measure Values to Text. Add Measure Names to Filter and select Total Value, Won, Lost, Outstanding. Manually re-order the columns.

Format Total Value to be a number displayed in millions with 1 decimal place & prefixed with $.

Add Measure Names to Text and adjust the text as required. Align the text to be centred.

Remove the row banding, and hide the column heading.

Then arrange the two sheets on the dashboard, ensuring both are set to fit entire view.

My published viz is here.

Hope you enjoyed this

Happy vizzin’!

Donna

Can you visualise survey data?

Donna Coles1 Comment

It was Sean’s first challenge of 2023, and he asked us to recreate this chart . Sean referred to it in the requirements as a floating bar chart, others may refer to it as a diverging bar chart or likert chart.

Modelling the data

Fake survey data was provided which was structured with each question in a column

The first thing we need to do is to pivot the data, so we have 1 row per question, per respondent. On the data source tab in Tableau Desktop, multi-select the question columns (Ctrl-click), then click the context menu for one of the selected columns and select Pivot

Due to the width of the screen and the number of columns, you may have to do this in multiple steps. Just multi-select the next set of columns, and select Add Data to Pivot from the context menu instead.

Once complete you should have a data set with 3 columns

Rename Pivot Field Names to Question and Pivot Field Values to Response.

Building the calculations

All the questions have 5 options to respond to ranging from Strongly Disagree -> Disagree -> Neutral -> Agree – > Strongly Agree. Sean hinted we would need to group the questions based on their sentiment. Some questions were worded in a positive way, which meant ‘agree’ responses could be taken as a ‘good thing’. While other questions were worded in a negative way, meaning agree’ responses were actually a ‘bad thing’.

So the first thing to do was to group the questions. I used the group functionality (right click on Question -> Create -> Group).

Sentiment

All the questions where I deemed an ‘agree’ answer was a favourable response to the product I grouped under Positive.

All the question where I deemed an ‘agree’ answer was a non-favourable response to the product I grouped under Negative.

I then wanted to bucket the responses into 3 groups – Disagree, Neutral and Agree – based on the combination of the actual Response, and the Sentiment grouping.

Response Group

IF [Sentiment] = ‘Positive’ THEN
IF CONTAINS([Response],’Disagree’) THEN ‘Disagree’
ELSEIF CONTAINS([Response], ‘Agree’) THEN ‘Agree’
ELSE ‘Neutral’
END
ELSEIF [Sentiment] = ‘Negative’ THEN
IF CONTAINS([Response],’Disagree’) THEN ‘Agree’
ELSEIF CONTAINS([Response], ‘Agree’) THEN ‘Disagree’
ELSE ‘Neutral’
END
END

With this I am essentially swapping disagree responses against questions of a negative sentiment into the agree/positive grouping and vice versa.

Finally I need another couple of calculations just to sense check were we are

Count Respondents

COUNTD([Id])

Question – Display

TRIM( SPLIT( [Question], “-“, -1 ) )

This removes the text that precedes the ‘-‘ from the Question string. I actually created this field using the Split functionality. Right click on Question -> Transform -> Custom Split. Use the separator – and split off the Last 1 columns

This will generate the field int he dimensions pane which I then renamed.

If we put all these out into a table, we can see how the data is shaping up

The next step is to work out the NPS values. When I’ve dealt with NPS in the past, I’ve referred to terms such as Promoters and Detractors, with an NPS score being computed as (Number of Promoters – Number of Detractors)/Total No of Respondents.

In this case a Promoter is anyone who is in the agree bucket.

Promoters

IF [Response Group]=’Agree’ THEN 1 ELSE 0 END

while a Detractor is anyone who is in the disagree bucket

Detractors

IF [Response Group]=’Disagree’ THEN 1 ELSE 0 END

The total number of respondents is

Total Respondents

{FIXED:COUNTD([Id])}

So I can now calculate

NPS

(SUM([Promoters])-SUM([Detractors]))/[Count Respondents]

Format this to percentage with 0 dp.

Let’s put this into another table so we can validate the data.

Ok, so this gives us the data required to plot the circle marks.

But we need to work out what’s need to plot the bars. And for this we need to know the respondents who are neither Promoters or Detractors.

Neutrals

IF [Response Group] = ‘Neutral’ THEN 1 ELSE 0 END

We are going to plot 2 bars for each row. 1 bar that represents the proportion of respondents who are in the positive side and 1 bar to represent the proportion of respondents who are in the negative side. The positive side mainly consist of the promoters, while the negative is the detractors. However, we need to consider the neutrals too, and we do this by halving the number and making half positive and half negative. Let’s see what I mean

# Respondents – Positive

(SUM([Promoters]) + (SUM([Neutrals])/2))/SUM([Total Respondents])

format this to percentage with 0 dp.

Similarly

# Respondents – Negative

-1 * ((SUM([Detractors]) + (SUM([Neutrals])/2))/SUM([Total Respondents]))

format this to percentage with 0 dp.

In this case the result is multipled by -1 as we want the bar to be plotted on the negative side of the axis.

Let’s pop these into the table too.

Ok. So now we have the core building blocks required to start building the viz.

Building the Likert Chart

Add Question – Display to Rows and # Respondents – Positive to Columns.

Click and drag # Respondents – Negative onto the canvas and drop the pill on the bottom axis when you see the 2 green columns icon appear.

This will automatically add the pill onto the same axis, and the view will update to use Measure Names and Measure Values

Remove Measure Names from the Rows and the bars will all appear on the same row. Sort the Question – Display field by NPS descending.

Add Response Group to the Colour shelf and adjust colours accordingly (using the Nuriel Stone palette). I also set the transparency to about 70%. You may need to reorder the values of Response Group – just do this manually in the colour legend if need be.

Increase the width of each row a bit, expand the Question – Display column, and remove the tooltips from displaying. Format the Question – Display column so it is aligned left and the font is a bit bolder/darker. Remove the Question – Display column heading (right click label > hide field labels for rows).

Format the axis to display as percentages to 0 dp, then edit the axis and

  • remove the title
  • fix the axis from -1.05 to 1.05
  • fix the tick marks to display every 0.5

Adjust the row banding to show, and remove any column/row dividers. Remove all gridlines. Adjust the column axis rulers and tick marks to be a solid dark line. Set the zero line for the columns to be a wider solid white line – it should just about be visible through the viz.

Now we need to label the agree and the disagree sections only. For this we need additional fields

Label – Positive

If MIN([Response Group]) = ‘Agree’ THEN [# Respondents – Positive] END

and

Label – Negative

If MIN([Response Group]) = ‘Disagree’ THEN [# Respondents – Negative] END

Add both these fields to the Label shelf and arrange side by side. Manually adjust the colour of the font to a dark grey.

This is the core design of a likert chart. It could be bult using dual axis too, but that wasn’t an option in this instance, as we need to add the additional NPS circles to the chart.

Adding the NPS circles

Add NPS to Columns. On the NPS marks card, remove all the fields, and change the mark type to circle. Add NPS to the Colour shelf, and adjust the colours so they range from the same yellow/green to blue/teal colours already used. The easiest way to do this is to click on the coloured square at each of the edit colour dialog and use the pick screen colour option to select the colours already used.

Set the colour to be 100% opacity and add a white border around the circles (via the Colour shelf)

Set the chart to be dual axis and synchronise the axis. Change the mark types back to bar and circle accordingly if they changed.

Now we can see some of the labels on the bar chart are positioned where the circles are/very close to them. Manually move those affected – click on the section of the bar to highlight it, then click on the label and when the cursor changes to a crosshatch, drag the label to where you want. Note – this isn’t something I would typically do if the data was to change behind this viz without any manual involvement.

Hide the top axis (uncheck show header) and remove all row/column dividers.

Now we just need to label the circles. I need additional fields for this. Firstly, the NPS field is on the wrong format – it’s actually stored as a decimal, formatted to a percentage. Secondly, I ended up with two label fields, due to the colouring. Sometimes when you add a label to a mark, the colour of the text may display black or white depending on the contrast with the underlying colour. Tableau automatically does this, and it isn’t something you can really control. As soon as you start manually adjusting any colours, it can be hard to get back to the right state. I fiddled around trying to get things to work properly for a while using the auto colouring (which involved rebuilding by starting with the NPS dot plot and then adding the bars), but it still wasn’t a ‘cast-iron’ solution. So I ended up creating

Label -NPS-Black

IF [NPS] * 100 <=70 THEN [NPS] *100 END

Label-NPS-White

IF [NPS]*100 >70 THEN [NPS]*100 END

I just chose an arbitrary ‘cut off’ for when the colour might change.

Add both these fields to the Label shelf of the NPS marks card, and arrange them side by side. Edit to font so the Label-NPS-White field is coloured with white font, and the other black. Set the alignment of the text to be middle centre. Adjust the size of the circles if required. Verify tooltips don’t exist on any marks.

Stopping the chart from being clicked

In the solution, if you try to click on the bar chart, you get no interaction – nothing highlights, like it does if you click on the axis or the questions. On a dashboard, this is managed by adding a floating blank object and carefully placing it over the top of the section you don’t want to be clickable.

And that should be it! My published viz is here.

I have to admit I did have a few false starts when building this out, and its highly probable this could have been created without all the fields I ended up with. It’s sometimes just the path your brain follows, and there’s nothing wrong with that!

Happy vizzin’!

Donna

Calculating Year-on-Year Percentage Change

Donna Coles1 Comment

In this week’s #WOW2023 challenge, Erica asked us to show the data for the selected year for a set of EU countries, but within the tooltip, provide additional information as to how the data compared to the same month in the previous year.

A note about the data

For this we needed to use the EU Superstore data set, a copy of which was provided via a link in the challenge page. Since part of validating whether I’ve done the right thing is to have the same numbers, I often tend to use any link to the data provided, rather than use any local references I may have to data sets (ie I have so many instances of Superstore on my local machine due to the number of Tableau instances I have installed). I did find however, that using the data from the link Erica provided, I ended up with a data set spanning 2015-2018 rather than 2016-2019. However I quickly saw that the numbers for each year had just been shifted by a year, so 2018 in Erica’s solution was equivalent to the 2017 data I had.

The viz is also just focussed on a subset of 6 countries. I chose to add a data source filter on Country to restrict the data to just those countries required (right click data source in the data pane -> Add data source filter).

Building out the required data calculations

The data will be controlled by two parameters relating to the Year and the Country

pYear

integer parameter, defaulted to 2017, displayed using the 2017 format (ie no thousand separators). 3 options available in a list : 2016,2017,2018

pCountry

string parameter defaulted to Germany. This is a list parameter and rather than type the values, I chose the option Add values from -> Country

We need to use the pYear parameter to determine the data we want to display, rather than simply apply a quick filter on Order Date, as we need to reference data from across years. Simply filtering by Order Date = 2017 will remove all the data except that for 2017, and so we won’t be able to work out the difference from the previous year. Instead we create

Sales Selected Year

ZN(IF [pYear] = YEAR([Order Date]) THEN [Sales] END)

Wrapping within ZN means the field will return 0 if there is no data.

Format this to € with 0 dp.

We can then also work out

Sales Prior Year

ZN(IF [pYear]-1 = YEAR([Order Date]) THEN [Sales] END)

which then means we can work out

Diff From PY

(SUM([Sales Selected Year]) – SUM([Sales Prior Year])) / SUM([Sales Prior Year])

custom format this to +0.0%;-0.0%;0.0%

This will display a positive change in the format +12.1%, a negative change as -12.1% and no change as 0.0%

Let’s pop all this information out in a tabular view along with the Country and Order Date to sense check the numbers

This gives us the core data to build the basic viz.

Core viz

Add Order Date at the Month date part level (blue pill) to Columns and Sales Selected Year to Rows and Country to Colour. Make sure it’s a line chart (use Show Me) if need be. Adjust the colours accordingly.

Amend the Order Date axis, so the month names are in the abbreviated format (right click on the bottom axis -> format)

Identifying the selected country

We need to change the colours of the lines to only show a coloured line for the selected country. For this we need

Is Selected Country

[Country]=[pCountry]

Add this field to the Detail shelf . Then click on the small icon to the left of the Is Selected Country pill, and select the Colour option.

This will mean that both Country and Is Selected Country are on the Colour shelf, and the colour legend will have changed to a combo of both pills

Move the Is Selected Country pill so it is positioned above the Country pill in the marks card section, and this will swap the order to be True | Country instead. Modify all the colours in the legend that start with False to be ‘grey’. Change the pCountry parameter and check the right colour combinations are displayed.

Change the Sort on the Is Selected Country pill so it is sorted by Data source order descending. This will ensure the coloured line is in front of the grey lines.

Adding the circles on the marks

We need a new field that will just identify the Sales for the selected country and selected year.

Sales Selected Year & Country

IF [Is Selected Country] AND [Year Order Date]=[pYear] THEN [Sales] END

Add this to Rows, then make the chart dual axis and synchronise the axis. Change the mark type of the Sales Selected Year & Country marks card to a circle, and adjust the Size to suit.

Finalising the line chart

Add Diff From PY onto the Tooltip shelf of the All marks card.

Create a new field

Month Order Date

DATENAME(‘month’, [Order Date])

and also add this to the Tooltip shelf. Adjust the tooltip to match the required formatting.

Hide the right hand axis (uncheck Show header).

Edit the left hand axis and delete the title, fix the axis from 0 to 50,000 and verify the axis ticks are displaying every 10,000 units.

Hide the 60 nulls indicator (right click -> hide indicator).

Remove the row & column dividers. Hide the Order Date column heading (right click -> hide field labels for columns)

Create the Country name for the heading

On a new sheet

  • Add Country to Rows
  • Add Is Selected Country to Filters and set to True
  • Add Country to Colour and then also add Is Selected Country to colour in the way described above.
  • Add Country to Label
  • Adjust the formatting of the Text so it is much larger font.

Hide the Country column (uncheck show header), and remove all row/column dividers. Ensure the tooltip won’t display.

Putting it all together

I used a horizontal container placed above the core viz. In the horizontal container I added blank objects, a text object, and the Country label sheet. I adjusted the size of the objects to leave space to then float the parameters. The parameters were resized to around 25 pixels so they just displayed the arrow part of the parameter. All this was a little bit of trial and error, and I did find that after publishing to Tableau Public, I had to adjust this section again using web edit.

My published viz is here.

Happy vizzin’!

Donna