Tag: Parameters

Dynamic Dimension Colouring

Donna ColesLeave a comment

For this week’s challenge, Yusuke asked us to provide a solution to allow charts to be coloured by different dimension, but he sprinkled a few extras in just for good measure 🙂

Defining the parameter

The key driver here is going to be the use of a parameter to define the dimension we need to colour by.

pColourBy

string parameter defaulted to Order Date, listing the 4 options as below

We then need a field that uses this parameter to define the actual dimension we’ll colour by

Colour

CASE [pColourBy]
WHEN ‘Order Date’ THEN STR(YEAR([Order Date]))
WHEN ‘Region’ THEN [Region]
WHEN ‘Category’ THEN [Category]
WHEN ‘Segment’ THEN [Segment]
END

Building the Order Date chart

On a new sheet, add Order Date to Columns and Sales to Rows. Change the mark type to Bar and add Colour to the Colour shelf. Adjust the colours to suit, set the opacity to 70% and add a white border. Show the pColourBy parameter.

Change the options in the pColourBy parameter and each time readjust the colours as you wish.

Add a reference line to the Sales axis that displays the value of Total Sales per cell

Format the reference line to format the displayed number in $M and bold font, and align top middle.

Create a new field

% of Sales

IF SUM([Sales]) / TOTAL(SUM([Sales])) <> 1 THEN SUM([Sales]) / TOTAL(SUM([Sales])) END

and format to % to 1dp. This will only display a value if its not 100%.

Add this to the Label. Adjust the table calculation setting so it is computing by the Colour field only.

Adjust the Label so the font is bold and the label only appears when Highlighted. Then update the Tooltip as required.

Although not explicitly called out in the requirements, I noted that if Yusuke clicked on the chart title, it reset the dimension to colour by. To deal with this we need to create

param Order Date

‘Order Date’

Add this to the Detail shelf.

We also need to ‘fake’ the title to be part of the chart itself (so it’s clickable). Double click into the Columns and manually type ‘Sales by Order Date’ and position the pill created before Order Date.

Right click on the column label (the text in darker font) and hide field labels for columns. Then right click on the column label to format – set the font to 12pt and bold, align left and shade the background to light grey. Increase the width of the column heading.

Then right click on the corner whitespace next to the heading just created, and format. Apply a light grey shading to the corner too.

If the ‘title’ is clicked, we don’t want it to be ‘highlighted’/’selected’. For this we will need fields

True

TRUE

False

FALSE

Add both of these to the Detail shelf.

Finally tidy up by removing the axis title, adjusting the font of the axis labels (I made them a bit darker), and removing row & column dividers. Name the sheet Order Date or similar.

Building the Region chart

Duplicate the Order Date chart and then click the option in the menu to swap axis so we have a horizontal bar chart.

Move the ‘Sales by Order Date’ pill from Rows to Columns and update the text to become ‘Sales by Region’ instead. Drag the Region pill and drop it directly over the Order Date pill on the Rows so it replaces it and all references to the field are replaced too. Widen the rows.

Right click on the ‘Region’ text in the column heading and hide field labels for rows. Format the reference line to align middle right.

Create a new field

param Region

‘Region’

and add this to the Detail shelf instead of the param Order Date field. Name the sheet Region or similar

Building the Category Chart

Duplicate the Region chart, and go through similar steps described above so the ‘title’ is Sales by Category and a new field

param Category

‘Category’

replaces param Region on the Detail shelf.

Building the Segment Chart

Repeat as above, this time setting the ‘title’ to Sales by Segment and a new field

param Segment

‘Segment’

replaces param Region on the Detail shelf.

Adding the interactivity

Add the sheets to a dashboard using layout containers and padding to organise as required. Then create the following dashboard actions

Highlight Action : Highlight

On hover of any of the charts on the dashboard, target all other charts, highlighting based on the Colour field only.

This action makes all the % labels appear when the mouse cursor is moved over the bars.

Parameter Action : Set Order Date Param

On Select of the Order Date sheet, set the pColourBy parameter with the value from the param Order Date field.

Parameter Action : Set Region Param

On Select of the Region sheet, set the pColourBy parameter with the value from the param Region field.

Parameter Action : Set Category Param

On Select of the Category sheet, set the pColourBy parameter with the value from the param Category field.

Parameter Action : Set Segment Param

On Select of the Segment sheet, set the pColourBy parameter with the value from the param Segment field.

These actions change the value displayed in the pColourBy parameter when the ‘title’ of the charts is clicked on.

Filter Action: Deselect Order Date Title

On select of the Order Date sheet on the dashboard, target the Order Date worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.

Filter Action: Deselect Region Title

On select of the Region sheet on the dashboard, target the Region worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.

Filter Action: Deselect Category Title

On select of the Category sheet on the dashboard, target the Category worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.

Filter Action: Deselect Segment Title

On select of the Segment sheet on the dashboard, target the Segment worksheet directly, passing the selected values of True = False. Show all values when selection is cleared.

And once these have all been applied, you should have a functioning dashboard. My published version is here.

Happy vizzin’!

Donna

Competitor Comparison

Donna ColesLeave a comment

Erica set this week’s challenge, focusing on the ability to compare specific entities against themselves and ‘the whole’ without resulting in a mess of coloured spaghetti. 3 levels of difficulty were provided. As it stated the levels didn’t necessarily follow on from each, I just built (and am therefore blogging about) level 3 – the advanced challenge.

Defining the core parameters

For the user to select the main element they want to analyse we need

pPrimarySubCat

string parameter, that is sourced from a List based on the Sub-Category field when the workbook opens. Default to Binders.

This parameter will be visible to the user to select from a drop down list control.

To capture the secondary element to compare against, we need

pSecondarySubCat

string parameter defaulted to Bookcases.

This is just a ‘type in’ field, that won’t ultimately be displayed to the user, but populated via a dashboard parameter action on select of a line in the chart.

To control the different type of display options, we need

pDisplay

integer parameter sourced from a manual list which aliases the integer values for the displayed text strings. Defaulted to 2 (Difference from Primary)

Defining the additional calculations

As I often do, we’ll build out a tabular display to determine all the calcs required. On a new sheet, add Region and Sub-Category to Rows, then add Order Date at the Quarter level as a discrete (blue) pill to Columns. Add Sales to Text. Show the 3 parameters created above.

We need to identify which Sub-Categories will be coloured. This is based on whether they are a primary or secondary Sub-Category.

Is Primary or Secondary Sub Cat

[pPrimarySubCat] = [Sub-Category] OR [pSecondarySubCat] = [Sub-Category]

Add this to Rows. Based on existing selections, the rows for Binders and Bookcases should be set to True.

We will also need to identify which is the the Primary Sub-Category only to help determine how many rows are displayed, so create

Is Primary SubCat?

[pPrimarySubCat] = [Sub-Category]

Add to rows. In this case just Binders should be True at this point.

With this field, we can then work out how many ‘rows’ are going to be in our final viz display.

Display Row

IIF([pDisplay] = 0, TRUE, [Is Primary SubCat?])

ie, if the pDisplay parameter is ‘Raw values – overlay’ , then we’ll just display 1 row (so all rows set to True), otherwise there will be 2 rows, split based on whether the Sub-Category is the selected value in the pPrimarySubCat parameter or not.

Add this to Rows, and change the pDisplay parameter to see how this field changes.

We also need to display different values depending on what pDisplay option is selected. When the ‘Difference from Primary’ option is selected, then we need to show the Sales value for the primary Sub Category, but the difference from this value for all others. For this we first need to capture just the sales for the primary Sub-Category

Sales For Primary Sub Cat

IF [Is Primary SubCat?] THEN [Sales] END

Add to the table and adjust Measure Names so it is displayed after the Order Date field. Rows for this column will only have values when the Sub-Category is the primary one selected.

Now we calculate the difference, but only if it’s not the primary Sub-Category; we want Sales in that instance

Sales Difference

IF MIN([Is Primary SubCat?]) THEN SUM([Sales])
ELSE SUM([Sales]) – WINDOW_MAX(SUM([Sales For Primary Sub Cat]))
END

Here we’re using a WINDOW_MAX table calc to essentially ‘spread’ the value in the Sales for Primary Sub Cat column across all rows associated to the Region. Add this to the table, and adjust the table calculation setting of the pill, so it is computing by all fields except Region and Order Date

Finally, we need a field that will decide whether we’re displaying Sales or Sales Difference based on the pDisplay selection

Value to Display

IIF([pDisplay]=2, [Sales Difference ], SUM([Sales]))

Again, add to the table, adjust the table calc as above and then test the output of the field, as you adjust the pDisplay parameter.

While we’re here, we’ll just define another couple of calcs needed for the viz

Label Sub Cat

IF [Is Primary or Secondary Sub Cat] THEN [Sub-Category] END

Used to only display a label for either of the two selected Sub-Categories.

Tooltip – Value Label

IIF([pDisplay]=2 AND NOT([Is Primary SubCat?]), “Difference from ” + [pPrimarySubCat] + ” Sales”, “Sales”)

Will be used on the Tooltip to ensure the correct text is displayed depending on type of display selected.

Building the Viz

On a new sheet, show the 3 parameters and set them to the defaults (ie Binders, Bookcases and Difference from Primary).

Add Region to Columns, then add Order Date at the Quarter level as a continuous (green) pill to Columns. Add Display Row to Rows and adjust the Sort on the pill to be a manual sort, where True is listed first. Add Sub-Category to Detail, then add Value to Display to Rows and adjust the table calc so all fields except from Region and Order Date are selected.

Add Is Primary or Secondary Sub Cat to Colour. Some lines will disappear, but don’t worry. Then add Region to Detail, and then select the ‘detail’ icon to the left of the pill on the marks shelf, and change it to Colour so 2 pills are now on the Colour shelf. Adjust the table calculation setting of the Value to Display pill to ensure the Is Primary or Secondary Sub Cat field is also now checked – this should make all the lines reappear.

Then adjust the colours in the colour legend so all the entries that start ‘False’ are grey and the others are as required.

Adjust the sort on the Is Primary or Secondary Sub Cat pill on the marks card, so it is manually sorted with True first. This ensures the coloured lines are ‘on top’ and always visible. Add Is Primary SubCat? to Size shelf. Readjust the table calc on Value to Display again, and then adjust the Size so it is visibly thicker than the rest of the lines, which will probably be by adjusting both the range in the Size legend, and adjusting the slider on the Size shelf.

Add Label Sub Cat to the Label shelf (adjust table calc again), and set label to allow labels to overlap other marks. Add Tooltip – Value Label to tooltip and update the Tooltip as required

Add a reference line to the Value to Display axis, and set to be a constant of 0 displayed as a black dashed line

Edit both axis to update the axis titles on each, hide the Display Row pill (uncheck show header on the pill) and hide the Region column label (right click > hide field labels for columns).

Building the dashboard

Use layout containers to construct the dashboard as required

Create a dashboard parameter action to capture the value of the secondary Sub-Category

Set Second Sub Cat

On select of the Viz, set the pSecondarySubCat parameter with the value sourced from the Sub-Category field. When selection is cleared, set it <none>

Clicking one of the grey lines should now change the comparison Sub-Category. But you’ll notice the rest of the unselected lines are ‘faded’ and your selection is ‘highlighted’. We don’t want this to happen. To resolve, create new calculated field

HL

‘Dummy’

and add to the Detail shelf on the viz sheet itself.

Then add a dashboard highlight action

Un-Highlight

On selection of the Viz sheet on the dashboard, target the viz sheet on the dashboard, selecting the HL field only.

As all the marks have the HL ‘dummy’ field associated to them, they all become ‘highlighted’, giving the appearance of nothing actually being highlighted.

Finally, we need to make the title of the dashboard ‘dynamic’ and reflective of the selections made in the primary and secondary Sub-Category parameters. But the secondary one can be empty, so the text needs to handle this. An additional ‘ and ‘ needs to display if the secondary Sub-Category is set. I chose to use a parameter to help with this, as text objects on a dashboard can reference parameters.

Create a new parameter

pTitle-and

string field defaulted to the text <space>and<space>

Create a calculated field

Param-and

‘ and ‘

and add to the Detail shelf on the viz. Set it to be an attribute (this won’t impact the table calc).

Back on the dashboard, create another dashboard parameter action

Set ‘and’

on select of the Viz, set the pTitle-and parameter passing in the value from the Param-and field. When the selection is cleared, set to <none>.

Then create (or adjust) the title text object so it references the relevant parameters (notice the spacing – or lack of – between some of the fields)

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

Happy vizzin’!

Donna

Can you use Dynamic Colour Ranges?

Donna ColesLeave a comment

Yusuke set the challenge this week which allowed us to try out a new feature of Tableau – Dynamic Colour Ranges. As a consequence, you’ll need v2025.2 for this.

Setting up the Data for use in a Map

After connecting to the CSV file provided by Yusuke, I had to set the Region field to have a Geographic Role of State/Province

I then double-clicked on Region to generate the map, but due to my location settings, no information displayed.

To resolve this, Edit Locations (via Map menu) and change Country/Region to Japan.

The Your Data – Matching Location fields should then match, and pressing OK presents a map.

Building the Basic Viz

Move Time from the ‘Measures’ section of the Data pane into the ‘Dimension’ section (drag it to be above the line). Format the Time field to be a custom number with 0 dp and to not show ‘,’ as a thousand separator. Then add Time to Filter and select 2016. Add Voting Ratio of election… to Colour. Adjust the Tooltip.

Edit the Colour Legend and choose a diverging colour palette (eg Red-Blue Diverging), then adjust the start and end colours as required

At this point, if you now make a selection on the map, the colours will remain as they are based on the current range

But what we want to happen, is for the colours to reflect a range based on just the selection (dynamic colour range). For this we need to create parameters

pMinValue

float defaulted to 45.5

pMaxValue

float defaulted to 62.9

Edit the Colour Legend again, and this time set the Start and End fields to reference the pMinValue and pMaxValue parameters.

Add the sheet onto a dashboard. Add a dashboard parameter action

Set Min Value

on select of the sheet, set the target parameter pMinValue to the minimum value from the Voting Ratio of election..

Create another dashboard parameter action

Set Max Value

on select of the sheet, set the target parameter pMaxValue to the maximum value from the Voting Ratio of election..

With this you should now find that when making a selection, the colour range is defined by the minimum and maximum values of just the marks selected

However the downside of this, is if you deselect the marks, the range doesn’t reset, and you then see the whole map coloured based on this more restricted range

Bonus – Building a ‘reset button’

On a new sheet, add Time to Text. Go back to the Map sheet, and set the Time filter to apply to ‘selected worksheets’, and select the new sheet you’re working on.

Change the mark type to Shape and choose a transparent shape (see here for details on how to set this up). Set the display to Entire View, then update the text in the Label (I sourced an arrow character from here). Align the text middle centre, set the background of the worksheet to blue and then update the font of the label text to white.

The intention is when the ‘button’ is clicked, we will set the pMinValue and pMaxValue parameters with the smallest and largest values associated to the year selected. His means we need to have some values on the ‘button’ sheet to pass to the parameters. So we need

Min Value for Year

{FIXED [Time]: MIN([Voting ratio of election for the House of Councillors (Single constituencies) %])}

and

Max Value for Year

{FIXED [Time]: MAX([Voting ratio of election for the House of Councillors (Single constituencies) %])}

Add both of these to the Detail shelf. Hide the Tooltip.

Add this sheet as a floating object to the dashboard. Show the Time filter from either the Map or Button sheets.

Add dashboard parameter actions, similar to the ones we did before

Reset Min Value for Range

on select of the Button sheet, set the target parameter pMinValue to the minimum value from the Min Value for Year field

Reset Max Value for Range

on select of the Button sheet, set the target parameter pMaxValue to the Maximum value from the Max Value for Year field.

Now if you ‘click’ the button sheet, the range should reset to the complete range for the relevant year.

Create the Label

Create a new sheet and add Time to Text. As before set the Time filter from another sheet to apply to ‘selected worksheets’, and select the new sheet you’re working on. Change the mark type to Shape and choose a transparent shape. Set the display to Entire View, and align the text middle centre. Hide the Tooltip.

Create a parameter

pSelectionMade

integer parameter, defaulted to 0

Create a field

Selection Made

1

and another field

Reset Selection

0

Move both fields into the Dimension section of the data pane.

On the Map sheet, add Selection Made to the Detail shelf.

On the Reset Button sheet, add Reset Selection to the Detail shelf.

Create a new field

Label: Selection Made

[pSelectionMade]=1 THEN ‘, Selected Prefecture(s)’ END

Add Label: Selection Made to the Label shelf and adjust the text. Set the background of the sheet to transparent (None)

Add the sheet to the dashboard and create the following dashboard parameter actions

Set Label

on select of the Map sheet, set the target parameter pSelectionMade to the Selection Made field, with no aggregation.

Clear Label

on select of the Reset Button sheet, set the target parameter pSelectionMade to the Reset Selection field, with no aggregation.

Final steps are to then arrange the dashboard as required using floating containers to store the filter, legend, ‘button’ and Label sheets. You’ll also need to change the filter to a single value list and customise so ‘All’ isn’t an option.

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

Note – I did find I needed to adjust the positioning of the floating objects via Tableau Public after publishing.

Happy vizzin’!

Donna

Can you filter or drill to details?

Donna ColesLeave a comment

Sean set this week’s challenge to give an alternative solution to displaying a table of details rather than the traditional ‘pancake table’ (his words not mine 🙂 ).

The main crux of the challenge relates to the dashboard actions and interactivity, so I’ll be brief(ish) in describing how to build the charts.

Creating the line chart

Add Order Date to Columns at the month-year continuous (green pill) level. Add Sales to Rows. Format Sales to $ with 0 dp. Remove the title on the Order Date axis. Update the Tooltip to give an instruction to ‘click the line to filter’. Rename the sheet Sales Trend or similar.

Creating the bar chart

Add Sub-Category to Rows and Sales to Columns. Sort by Sales descending. Hide the Sub-Category row heading label (right click > hide field labels for rows). Update the Tooltip to give an instruction to ‘click the bar to filter’. Rename the sheet Sales by Sub Bar or similar.

Creating the Tree Map

Add Segment and Ship Mode to Detail and Sales to Size. Move Segment to Colour and reduce opacity to about 60%. Move Ship Mode to Label and then add additional Segment and Sales pills to Label. Add a table calculation against the Sales pill on the Label shelf, so it is applying a percentage of Total by Segment only.

Add another instance of the Sales pill to Label and then update the layout of the label.

Move the Segment pills on the marks shelf so they are positioned below the Ship Mode to ensure the tree map is segmented based on the Ship Mode (there should be four blocks divided by the thicker white lines).

Update the Tooltip to give an instruction to ‘click the treemap to filter’. Rename the sheet Treemap or similar.

Build the Details table

On a new sheet add Order ID, Customer Name, Order Date (as a discrete exact date – blue pill), Ship Date (as a discrete exact date – blue pill) and Product Name to Rows. Add Sales to Text. Format Profit to $ with 0 dp and drag onto the canvas over the columns of Sales numbers, and release the mouse when the Show Me option appears. Add Discount into the Measure Values section. Change the aggregation to Average and then format to be % to 0 dp. Rearrange the order of the pills in the Measure Values section as required. Add Segment, Sub-Category and Ship Mode to the Detail shelf. Update the title to reference these 3 pills. Hide the Tooltip. Rename the sheet Details or similar.

Building the additional calculations needed

In clicking around Sean’s solution, I was finding what I had initially built wasn’t quite doing what Sean did. If I clicked on the bar chart and then the tree map, the details were only filtered based on the tree map and vice versa. There were ways to solve this, but this then resulted in other issues, in that after closing the details table, the charts remained filtered, but it wasn’t obvious as nothing was highlighted. Basically what I’m trying to say, is the filtering seemed like it should be straightfoward, but wasn’t. I ended up using a combination of parameters and filter actions.

So we’ll start by dealing with the parameters we need.

Create the following parameters

pSelectedDate

date parameter defaulted to 01 Jan 1900

pSelectedSegment

string parameter defaulted to <emptystring>

pSelectedShipMode

string parameter defaulted to <emptystring>

pSelectedSubCat

string parameter defaulted to <emptystring>

Then create the following calculated fields

Filter: Date

[pSelectedDate] = #1900-01-01# OR [pSelectedDate]=DATETRUNC(‘month’,[Order Date])

add this to the Filter shelf on the bar chart, tree map and details sheets and set to True.

Filter: SubCat

[pSelectedSubCat]=” OR [pSelectedSubCat]=[Sub-Category]

add this to the filter shelf on the line chart, tree map and details sheets and set to True

Filter: Segment

[pSelectedSegment]=” OR [pSelectedSegment]=[Segment]

add this to the filter shelf on the line chart, bar chart and details sheets and set to True

Filter: Ship Mode

add this to the filter shelf on the line chart, bar chart and details sheets and set to True

We also need a parameter to capture when we want to show the details table.

pClickMade

boolean parameter defaulted to False.

and to supplement it, we need a calculated field to use to set this parameter to true

Click Made

TRUE

Add Click Made to the Detail shelf of the line chart, bar chart and tree map.

We’ll set these parameters later.

Building the Close icon

The ‘close’ cross when the details sheet is displayed is another sheet. On clicking on it, we will want to set the pClickMade parameter to False so the Details will no longer show. For this we will need

Close

FALSE

Add this field to the Detail shelf on a new sheet. Change the mark type to shape and change the shape to a X. Set the colour to black and set to fit entire view. Hide the Tooltip. Name the sheet Close or similar.

Building the dashboard and interactivity

Using layout containers, arrange the line chart, bar chart and tree map into a dashboard. Use padding and background colours to get the layout as desired.

The add the Details sheet as a floating object and position over the top of the other charts. Set the background to white and add a black border. Also float the Close sheet into position too. Hide the title and also add a black border.

Select the Close sheet object, and then from the Layout tab in the left hand nav, check the Control visibility using value checkbox and select the pClickMade parameter

It should disappear if the parameter is still set to false. Repeat the same process with the Detail sheet object.

Now create the following dashboard parameter actions

Filter Month

On select of the Sales Trend sheet, target the pSelectedDate parameter, passing in the value from the Order Date. When the selection is cleared, reset to 01 Jan 1900.

Filter SubCat

On select of the Sales by Sub Bar sheet, target the pSelectedSubCat parameter, passing in the value from the Sub-Category. When the selection is cleared, reset to <emptystring>.

Filter Ship Mode

On select of the Treemap sheet, target the pSelectedShipMode parameter, passing in the value from the Ship Mode. When the selection is cleared, reset to <emptystring>.

Filter Segment

On select of the Treemap sheet, target the pSelectedSegment parameter, passing in the value from the Segment. When the selection is cleared, reset to <emptystring>.

Drill to Details

Via the menu of the Sales by Sub Bar, Sales Trend, and Treemap sheets, target the pClickMade parameter passing in the value from the Click Made field. When the selection is cleared, set the value to False.

Close Details

On select of the Close sheet, target the pClickMade parameter, passing in the value from the Close field. When the selection is cleared, keep the value.

If you start clicking around, you should find that all these actions do provide some level of filtering, but if you for example, click on the bar (to filter the line and treemap), and then click on a section in the tree map and use the ‘Drill down to details’ menu option, the details table has lost the filtering of the bar chart as the bar has become unselected when the treemap chart was clicked.

To resolve this, apply filter actions to the line chart, bar chart and tree map objects (the quickest way to do this is just select the object on the dashboard and click the ‘filter’ icon in the context menu.

If you do this on all 3 sheets and then look at the list of dashboard actions you’ll see 3 ‘Filter x (generated)’ entries.

By applying this mix of filtering through ‘default’ dashboard filter actions in conjunction with parameters, I think you have a more complete and understandable experience. And you will have to explicitly unselect each of the marks you clicked on to remove that filter. I added instructions on the dashboard to aid with this.

My published viz is here.

Happy vizzin’!

Donna

Can you swap measures?

Donna Coles2 Comments

It’s back to the EFL this week for my #WOW challenge. Once again I’ve tried to provide a multi-level challenge – a version that uses some core skills and features, and a version that just pushes the display a bit further, just for fun. I’ll start with the core build and then use that as the base for the bonus challenge.

Setting up the parameters

The main crux of this challenge is measure swapping, so for this we need a parameter

pMeasure

integer parameter listing values 1 to 4 which are aliased as per the screen shot below. Default value is 1 (Points).

Note – you can use a string parameter with the actual words. I just chose to use this method to demonstrate the aliasing ability. Also when referencing this parameter in a CASE statement (which we’ll do shortly), using integer values for comparisons is slightly more efficient than string comparisons.

We also need the user to have the ability to select the team they want to track

pSelectedTeam

string parameter defaulted to your preferred team (I chose Chelsea). This is a List parameter that is populated from the Team field via the Add values from button

Building the calculations

We need to determine the measure to display based on the parameter selection

Measure to Display

CASE [pMeasure]
WHEN 1 THEN SUM([Cumulative Points])
WHEN 2 THEN SUM([Cumulative Goal Difference])
WHEn 3 THEN SUM([Cumulative Goals For])
WHEN 4 THEN SUM([Cumulative Goals Against])
END

We also will need to colour the bars based on the selected team

Is Selected Team

[Team]=[pSelectedTeam]

and we need to sort the data based on best to worst, but need to consider that for Goals Conceded, the higher the value the worse the team is.

Sort Measure

IF [pMeasure] = 4 THEN [Measure to Display]*-1 ELSE [Measure to Display] END

We only want to show teams once they start participating in a Season. For this, we need to identify the team’s 1st season in the EFL

1st Season per Team

{FIXED [Team]: MIN(IF [Cumulative Points] > 0 THEN [Season End Year] END)}

and then we can create a field we can filter on, based on this

Show Team

[Season End Year]>=[1st Season per Team]

We only want to show a label against the 1st team and the selected team, so create

Label to display

IF MIN([Is Selected Team]) OR FIRST()=0 THEN [Measure to Display] END

and finally, we need to display the value of the current season’s measure on the tooltip, as well as the cumulative value, so we need another case statement

Tootltip Measure

CASE [pMeasure]
WHEN 1 THEN SUM([Points])
WHEN 2 THEN SUM([Goal Difference])
WHEn 3 THEN SUM([Goals For])
WHEN 4 THEN SUM([Goals Against])
END

Building the core bar chart

On a new sheet, add Team to Rows and Measure to Display to Columns. Add Season End Year to Filter and select 1993. Add Show Team to Filter and select True. Apply a sort to the Team field to sort by the field Sort Measure descending.

Add Is Selected Team to Colour and adjust accordingly. Add Label to display to Label. Add Season and Tooltip Measure to Tooltip and update accordingly,

Show the pMeasure and pSelectedTeam parameters and the Season End Year Filter. Adjust the Season End Year filter control so that the All option isn’t available and it displays as a single value slider control.

Move the Season End Year filter control on by one value and notice how the chart transitions. Adjust the Animations settings (Format menu > Animations) to be sequential and slow

Finally tidy up the display by

  • hiding the Measure to Display axis (right click > uncheck show header)
  • hiding the Team row label (right click > hide field labels for rows)
  • widen each row a bit
  • hide gridlines, zero lines, axis rulers and axis ticks
  • add pale grey row dividers
  • set the background colour of the worksheet
  • adjust the font of the row labels – I used Tableau Book 8pt in colour #3e1756 (dark purple)

Name the sheet Core or similar and add to a dashboard setting it to Fit Entire View

Add a title to the dashboard that references the pSelectedTeam parameter.

My core viz is here.

Building the bonus viz

Start by duplicating the core viz sheet.

We’re going to use a gantt bar to simulate a central line for each row. This bar needs to extend to the largest measure in the table for every row, so this is the point we’ll plt

Max Measure

WINDOW_MAX([Measure to Display])

Add this to the Columns shelf, and on the Ma Measure markls card that gets added, remove the Is Selected Team from the Colour shelf, and change the mark type to gantt bar.

The bar needs to extend to the 0 or the minimum value in the window (if its less than 0), so we need a field to show the difference between the max and the minimum of 0 or the ‘window min’, but we need to multiple by -1 so the size extends in the right direction.

Max-Min Diff

([Max Measure] – MIN(WINDOW_MIN([Measure to Display]),0)) * -1

Add this to the Size shelf, and then update the size to be as small as possible. Remove Label to Display from the marks card and add a border the same colour as the background to the Gantt bar (via the Colour shelf) to make the line very narrow.

Add Team to the Label shelf and align centre left. Format the label text to be 8pt and dark purple. Then make the chart dual axis and synchronise the axis.

Right click the top axis and select move marks to back. Then hide both axis (right click, uncheck show header) and hide the Team pill on Rows too (again uncheck show header)

Verify the Tooltip on the gantt bar displays the same as on the main bar. If not add the relevant fields and adjust to match.

Tidy up the formatting by removing the row and column dividers. If need be adjust the colour of the Gantt bar to be a paler grey.

Change the measure value to Goal Difference and adjust the year to 2024 and check the display looks as expected, especially at the bottom – the gap between the label of the team at the bottom and the bar is minimal.

Add the chart to a dashboard – the simplest way is to duplicate the core dashboard and then use the swap sheets feature to quickly swap the main vizzes.

My published viz is here

Happy vizzin’!

Donna

Can you use containers and dynamic zone visibility?

Donna ColesLeave a comment

This week’s #WOW2025 challenge was set live as part of TC25. Unfortunately, this year I couldn’t be there in person to meet everyone, which for the last 3 years has been my conference highlight 😦

Anyway, Kyle set the challenge, and conscious of time, provided a starting workbook, so the focus could be on the container and DZV functionality. For those who nailed this, he added some additional interactivity with dashboard actions.

So the first thing is to download the starter workbook from the challenge page.

I’m going to attempt to build this in the order of Kyle’s requirements.

Layout out the dashboard

So the requirement states that no floating objects are allowed. Typically when I build a dashboard for business purposes or where the layout is a little complicated, I always start by adding a floating container sized to the exact dashboard size and positioned 0,0. I then add tiled objects into it. Doing this means I don’t end up with Tiled container objects on my dashboard (or if any get added when legends/filters get automatically added, I just move any items I want to retain and then delete the Tiled container).

However, as Kyle says ‘no floating’, I will build adding to the ‘default’ dashboard which means there will be containers on there I don’t really want.

Now blogging about containers is usually very tricky as it’s hard to explain where things need to go. So I’ll be supplementing this with a lot of screen shots – fingers crossed following along works out ok!

To start, create a dashboard sheet and resize to 1200 x 900 as required. Observe the item hierarchy section of the Layout pane as this is where you’ll see all the containers and objects as we add them to the dashboard.

The main structure of the display is split into 2 columns, so start by adding a horizontal layout container to the dashboard. Once added, add 2 blank objects side by side to give the basic layout. Adding blank objects helps when positioning the required objects and is recommended when dealing with layout containers, especially if you’re new to them. They will ultimately be deleted as we go. Rename the horizontal container H – 2 cols or similar (right click on the container in the item hierarchy  > rename).

Notice how a Tiled container has now also appeared on the dashboard, even though we only added a horizontal container.

The first column of the dashboard contains 2 charts – the Scatterplot and the Sales & Profit Ratio Comparison sheets – stacked on top of each other. For this, add a vertical layout container between the two blank objects. Rename this V – Col 1.

Add the Scatterplot sheet into the vertical container and then add Sales & Profit Comparison underneath it.

The various legends associated with these 2 sheets, automatically get added into their own vertical container on the right hand side. These aren’t required, so from the item hierarchy, select the Vertical container and then Remove from dashboard.

The right hand column of the display will show the Sales & Profit Ratio by Month sheet and another (hidden) chart that needs to be built.

Add another vertical container between the V – Col 1 container and the right hand blank object. Name this V – Col 2, and add the Sales & Profit Ratio by Month sheet and then another blank object underneath it. Once again remove the right hand vertical container that is automatically added with all the legends/filters.

Now we have the ‘core’ layout, the 2 blank objects we added to the horizontal container, H – 2 Cols, right at the start, can be removed, so hopefully you should have a layout organised as below.

Now add the dashboard title (Dashboard menu > Show Title, and then update the text). This will automatically add a vertical layout container around all the existing contents.

Building the Sales & Profit Ratio by Sub-Category bar chart

On a new sheet, add Sub-Category to Rows and Sales to Columns. Add Profit Ratio to Colour and adjust the colour legend to use the Red-Black Diverging colour palette. Hide the Sub-Category row label heading (right click > hide field labels for rows).

The bar chart needs to be filtered when a State in the Sales & Profit Ratio Comparison chart is clicked on, or when a Date is selected in the Sales & Profit Ratio by Month chart. However, I noticed when clicking around, that when clicking the Sales & Profit Ratio by Month chart, it filtered the above bar chart by both the State and Date. So based on this, create 3 parameters.

S&PR Comp State

String parameter defaulted to empty string

S&PR by Month State

String parameter defaulted to empty string

S&PR by Month Date

Date parameter defaulted to 01 Jan 1900 (essentially a null date)

Show these parameters on the sheet.

We want to filter the chart if the S&PR Comp State has a value and the S&PR by Month Date is the ‘null’ date (which means we’ve interacted with the Sales & Profit Ratio Comparison chart), or if the S&PR Monthly State has a value AND the S&PR by Month Date has a value (which means we’ve interacted with the Sales & Profit Ratio by Month chart). So create

Filter – S&PR by SubCat

([State Name] = [S&PR Comp State] AND ([S&PR by Month Date]=#1900-01-01#))

OR

(([State Name] = [S&PR by Month State]) AND (DATETRUNC(‘month’, [Order Date]) = DATETRUNC(‘month’, [S&PR by Month Date])))

Enter a State name into the S&PR Comp State parameter (eg New Jersey), then add the Filter – S&PR by SubCat field to the Filter shelf and set to True. The chart should change.

Verify the functionality by adding a state and date into the other parameters eg 01 March 2021 and Texas

Empty the state parameters and set the date back to 01 Jan 1900. Name the sheet Sales & Profit Ratio by SubCat. The chart contents will disappear.

Creating a dynamic title sheet

Originally I hoped to do this without using another sheet and just using the title of the bar chart, but I need the date to show nothing rather than Jan 1900 depending on the user interactivity, so a new sheet is required.

But for it, we need some additional calculated fields.

State for Title

IIF([S&PR by Month State]<>”,[S&PR by Month State], [S&PR Comp State])

We only want to show the name of the state once, and both parameters may have it set.

Date for Title

IF [S&PR by Month Date]=#1900-01-01# THEN ” ELSE DATENAME(‘month’,[S&PR by Month Date]) + ‘ ‘ + STR(YEAR([S&PR by Month Date])) END

Line

IF [S&PR by Month Date]<>#1900-01-01# THEN ‘|’ ELSE ” END

Add all 3 fields to the Detail shelf of a new sheet. Change the mark type to polygon. Update the sheet title as below

Name the sheet S&PR Title Sheet or similar

Adding the bar chart, title & legend to the dashboard

All 3 of these objects – the bar chart, the title sheet and the profit ratio legend need to show or hide based on interactivity. To do this in one step, we can encapsulate the 3 objects within containers within another ‘parent’ container and control the visibility on the ‘parent’ container.

Add a vertical container between the Sales & Profit Ratio by Month chart and the blank object. Name this V – S&PR SubCat Chart

Add the Sales & Profit Ratio by SubCat sheet into this. Then add another horizontal container and place it above the Sales & Profit Ratio by Sub Cat chart (making sure it’s within the V – S&PR Sub Cat Chart container. Rename this H – S&PR Sub Cat Title.

Add the S&PR by Title sheet into this horizontal container, and then click on the Profit Ratio legend on the right hand side and move this object to sit to the right of the title sheet. Then click on the right hand column containing all the remaining legends, and delete this container from the dashboard. Then remove the blank object that’s sitting beneath the Sales & Profit Ratio by SubCat sheet. You should have something like below…

Adjust the width of the S&PR Title sheet so its wider. Set the sheet to Fit Entire View. Then select the H – S&PR SubCat Title container and edit the height to be 90 px.

Hide the title of the Sales & Profit Ratio by SubCat sheet.

Hiding and showing the Sales & Proft Ratio by Sub Category section

Create a new calculated field

Show S&PR by Sub Cat

[S&PR by Month State]<>” OR [S&PR Comp State]<>”

On the dashboard, select the V – S&PR SubCat Chart container and on the Layout pane, check the Control visibility using value checkbox, and select the Show S&PR by Sub Cat field. Assuming all the parameters are set to their default values, then the whole section should disappear, although the container will still be selected.

To make the section show, we need to set the parameters using dashboard parameter actions.

Set S&PR Comp State

On select of the Sales & Profit Ratio Comparison sheet, set the S&PR Comp State parameter passing in the value of the State Name field. When the selection is cleared, set the value back to <emptysrting>

Click on a row in the Sales & Profit Ratio Comparison bar chart, and the Sales & Profit Ratio by SubCat chart should display, filtered to that State, with the selected state name in the title.

Click the state again, and the chart disappears.

Create 2 further dashboard parameter actions

Set S&PR by Month State

On select of the Sales & Profit Ratio by Month sheet, set the S&PR by Month State parameter, passing in the value from the State Name field. When the selection is cleared, set it back to <emptystring>

Set S&PR by Month Date

On select of the Sales & Profit Ratio by Month sheet, set the S&PR by Month Date parameter, passing in the value from the Month([Order Date]) field. When the selection is cleared, set it back to 01/01/1900

Now click on a point in the line chart, and the Sales & Profit Ratio by SubCat chart should display filtered to the relevant state and month

Adding the Additional Interactivity

When the Scatterplot is clicked, the State in the existing Scatterplot State parameter should be updated. Create a dashboard parameter action

Set Scatterplot State

On select of the Scatterplot sheet, set the Scatterplot State parameter, passing in the value from the State field. When the selection is cleared, retain the value

If you click around the scatterplot, the Sales & Profit Ratio by Month line chart and Sales & Profit Ratio Comparison charts should update.

But we don’t want the other marks on the scatter plot to ‘fade’. To solve this, create a dashboard filter action.

Deselect Scatter marks

On select of the Scatterplot sheet on the dashboard, target the Scatterplot sheet directly, setting the fields TRUE = FALSE. On clearing the selection, show all values.

Finally, the last requirement is to highlight the line in the Sales & Profit Ratio by Month chart associated to the State selected in the Sales & Profit Ratio Comparison chart. For this first create a dashboard set action to capture the selected state

Add State to Set

On select of the Sales & Profit Ratio Comparison sheet, target the State Name Set. Check the single-select only checkbox. Running the action should Assign value to set and clearing the selection should remove all values from set

Then add a dashboard highlight action

Highlight Monthly Trend Chart

On select of the Sales & Profit Ration Comparison sheet, target the Sales & Profit Ratio by Month sheet targeting the State Name field only

And hopefully, with all this, you should have a fully interactive dashboard. My published viz is here.

Happy vizzin!

Donna

Which products have low profit ratio?

Donna ColesLeave a comment

Community month continues this week with Yama G posing us this challenge to analyse profit ratio.

Building the basic bubble chart

The simplest way I found to start this, was to use ctl-click to multi-select Category, Region, Product Name and Sales from the data pane, and then select the packed bubbles option from the Show Me menu.

Then move Category from Text to Columns and Region from Text to Rows, and remove Category from Colour.

Create a new field

Profit Ratio

SUM(Profit)/SUM([Sales])

and apply a custom number format of 0%;â–²0%;0% (in this instance I think the intention is to use the â–² as ‘warning’ indicator).

Add this to Colour. To cluster the lower profit ratios towards the centre of the bubbles, add a sort to the Product Name pill on the Text shelf, to sort ascending by Profit Ratio.

Note – you can’t influence quite how the bubbles are arranged, so it’s possible the layout you see may differ slightly from the solution.

Showing details for selected Sub-Category

Clicking on a Category in the bubble viz should expand to show the Sub-Categories. To support this we need a parameter

pCategory

string parameter defaulted to Technology

The Category label displayed needs to show an arrow indicator based on whether the Category is selected or not

Label – Cat

[Category] + ‘ ‘ + IIF([Category]=[pCategory],’â–²’,’â–¼’)

Add this to Columns

We want to show Sub-Category details for the selected Category, so create

Label – SubCat

IIF([pCategory]=[Category], [Sub-Category], ”)

Add this to Columns too.

There is a need to capture the Region too, as part of the sorting on the tabular viz. But the Region header label needs to indicate the selected region. We will need a parameter

pRegion

string field defaulted to East

And then a new field

Region * Sortkey

[Region] + IIF([pRegion]=[Region],’*’,”)

Add this to Rows.

Finally tidy up by

  • adjusting the Tooltip
  • hide the Region pill (uncheck show header)
  • hide the Category pill (uncheck show header)
  • hide the Region * Sortkey row heading label (right click -> hide field labels for rows)
  • hide the Label – Cat / Label SubCat column heading label (right click -> hide field labels for columns)
  • remove the row dividers in the body of the viz (set the Level to 0)

Update the sheet title and name the sheet Bubble or similar.

Building the basic Product Detail table

The first column is a combination of fields, so create

Product Name (Cat, SubCat)

[Product Name] + ‘ (‘ + [Category] + ‘,’ + [Sub-Category] + ‘)’

Add Product Name, Product Name (Cat, SubCat) and Region * Sortkey to Rows and Order Date as a discrete (blue) pill at the Year level to Columns. Change the mark type to circle. Add Sales to Size and Profit Ratio to Colour. Add Profit Ratio to Label and align right. Set the table to Fit Width. Increase the Size of the circles a bit.

Add totals via Analysis > Totals > Show Row Grand Totals and then via the same menu, select the Row Totals to Left option. Edit the Colour Legend and select the Include Totals option, so the circles in the total column are also coloured.

Applying the table sort

The requirement is to sort each Product Name based on the value of the Profit Ratio associated to the selected Region. The data should be sorted ascending.

This took me a little while to figure out – initially I wanted to use a table calculation, but you can’t apply a sort to a field based on that, and if you then add it as a discrete measure, you can’t have total columns. So I figured it out eventually. Firstly, I need to capture the Profit Ratio for each Product Name and Region (essentially the value listed in the Grand Total column). I can use an LOD for this.

PR by Product & Region

{FIXED [Product Name], [Region]: SUM([Profit])/SUM([Sales])}

but I then only want the value associated to the selected Region, and I want to ‘spread’ this across every row for the Product Name. So I can use another LOD but just at the Product Name level, which in turn uses a nested IF statement, to just return the value we care about.

Sort

{FIXED [Product Name]: AVG(IIF([Region]=[pRegion],[PR by Product & Region],NULL))}

Apply a Sort to the Product Name pill in Rows to sort by the field Sort ascending

You should find that when you see some Product Names that have sales in the selected region (in this case East), that the products are listed based on this value with the lowest Profit Ratio first (note the East row isn’t listed first if there are sales for the Central region, as the rows within each product are listed alphabetically based on the region name.

Finally tidy up by

  • Adjusting the Tooltip to suit
  • Hide the Product Name pill (right click, uncheck show header)
  • Adjusting the font style of the fields and label headings.
  • Rename the Grand Total label to Total (right click the label > format and update the Label value)
  • hide the Order Date column heading label (right click > hide field labels for columns)
  • add row banding (right click viz to format), then adjust band size to level 1

Update the sheet title and name the sheet Table or similar.

Building the dashboard and adding the interactivity

Using layout containers, add the two sheets onto the dashboard, adding a title and the relevant legends (note I used text objects for the Profit Ratio legend title and the Sales ‘legend’.

We need several dashboard actions:

Filter Products

Dashboard filter action, that on select of the Bubble sheet, targets the Table sheet, passing through the Product Name only. The set of products is retained when the selection is cleared.

Set Category

Dashboard parameter action, that on select of the Bubble sheet, sets the pCategory parameter with the value from the Category field. When the selection is cleared, the parameter is reset to <empty string>

This action will allow the bubble chart to ‘expand and collapse’ on click.

Set Region

Dashboard parameter action, that on select of the Bubble sheet, sets the pRegion parameter with the value from the Region field. When the selection is cleared, the parameter is retained

This action will apply the ‘niche’ sort and the relevant region labels will be updated with an * too.

Finally, on selection of products in the bubble viz, we don’t want the other marks to ‘fade’ To stop this from happening, create a new field

Dummy

‘Dummy’

and add to the Detail shelf on the Bubble sheet.

Then add a dashboard highlight action

UnHighlight

On select of the Bubble sheet, target the Bubble sheet selecting the field Dummy only

And with that you should have a functioning dashboard. My published viz is here.

Note, after testing, I did notice a difference in the behaviour of my version to the solution. When I deselect all products from the bubble chart when in an expanded state, the section will collapse. The solution uses a ‘fake header’ sheet to stop this from happening, which is then carefully positioned above the bubble chart sheet. I’m ultimately happy with my 2-sheet solution, but feel free to check out the challenge solution for a better understanding.

Happy vizzin’!

Donna

Data Storytelling with Dynamic Zone Visibility

Donna ColesLeave a comment

This week’s community challenge was set by Chiaki to showcase how to use dynamic zone visibility to tell a story. We’ll step through each chart and then how it’s all put together.

Building the line chart

Create a new field

Profit Ratio

SUM(Profit)/SUM(Sales)

and format to % with 1 dp.

Add Order Date to Filter and restrict to Years 2023 and 2024 only.

Add Order Date to Columns as a discrete (blue) pill at the year level. Add Sales to Rows and Profit Ratio to Rows. Format Sales to be $ with 0 dp. Set the screen to Fit Width.

Add Sales to the Label shelf of the Sales marks card. Add Profit Ratio to the Label shelf of the Profit Ratio marks card.

Create a new field

Profit Ratio YoY

ZN([Profit Ratio]) – LOOKUP(ZN([Profit Ratio]), -1)

and format to % with 1 dp. Add to the label shelf of the Profit Ratio marks card

Right click on the 2024 Profit Ratio mark and select Annotate > Mark. Update the text as required and reference the value of Order Date year and the Profit Ratio YoY

Click and drag the annotation to reposition, and then right click on the annotation to format, and adjust the shading to pale orange and add a thick orange border.

Hide the Order Date column heading (right click > hide field names for columns). Add a title and name the sheet Line or similar.

Building the comet chart

On a new sheet, add Sales to Columns and Profit Ratio to Rows. Add Order Date to Filter and restrict to years 2023 & 2024 only. Add Sub-Category to Detail and change the mark type to Line.

Add Order Date to Detail as a discrete (blue) pill at the Year level, and add another instance of Order Date at the Year level to Size.

Add Profit Ratio to Colour. Add Profit Ratio YoY to Tooltip and adjust the table calculation so it is computing by Year of Order Date only.

Update Tooltip as required and then add annotations to the required marks, again making references to the relevant variables. Format as before, add a title and name the sheet Comet or similar.

Building the Sankey chart

On a new sheet, add Order Date as a discrete (blue) pill to Filter and restrict to years 2023 and 20204. Add Sub-Category and restrict to Tables and Binders only.

Create a new field

Profitable

IF [Profit] >0 THEN ‘Profitable’ ELSE ‘Unprofitable’ END.

From the dropdown in the marks card, select Sankey

Then add Profitable, Order Date and Region to the Level shelf in that order. Adjust the Sort of the Profitable pill, so its sorted descending and Unprofitable is listed before Profitable.

Add Profit Ratio and Orders(Count) to the Tooltip and update accordingly. Click Format Extension and set the Level Padding and the Link Padding to 10 each.

Click on the Unprofitable box to highlight it and the subsequent flow. Add a title and name the sheet Sankey or similar.

Controlling the story

Create a parameter

Page

integer parameter from 1-4 where each number is mapped to the relevant text. Default to the text associated to 1.

Create boolean fields

Page >=2

[Page]>=2

Page >= 3

[Page]>=3

Page = 4

[Page]= 4

Building the dashboard

You need to use layout containers to build the dashboard. When using these, it’s often useful to add blank objects to help preserve the type of container you want as you add things in. These then get removed. It can be quite fiddly to get right.

For the main body of the dashboard, the section with the charts in, I started with a horizontal container that then had a vertical container on the left and the Sankey on the right. The left hand vertical container then had the line chart on the top, and another horizontal container beneath it. That horizontal container then had the comet chart on the left and a vertical container on the right, which contained the colour and size legends.

I have a habit of renaming the layout containers in the item hierarchy section, to help me identify each section

The line chart object was formatted to have a grey border, outer padding of 10 and inner padding of 20. The vertical container around the comet chart and it’s legends, was also formatted the same. And the Sankey chart object was also formatted the same.

Add a title section, and show the Page parameter as a single value list. Format this with a grey background.

Click on the layout container that contains the comet chart and it’s legends, and then from the layout tab on the left hand side, check the control visibility using value and select the Page >=2 option.

If you page control is set to 1 (Problem Statement), then this section will now disappear. Set it to the next option for it to reappear.

Click on the Sankey object, and do the same, although this time select the Page >=3 option. Again changing through the page control options, the chart will appear and disappear.

With all three charts displaying, and once you’re happy with your layout, add a floating text box and resize to cover the main chart areas. Add the relevant text, set the background colour to grey with a 97% opacity, so the charts underneath can just be seen. Then set the visibility of this to use the Page =4 option.

Cycle through the page options and see how the display changes. My published viz is here.

Happy vizzin’!

Donna

Can you show the top X customers in EACH of the last 3 years and their contributions to Sales?

Donna ColesLeave a comment

For Community Month at #WOW2025 towers, Lorna presented a challenge one of her colleagues had brought to her which they solved together. The need is to identify the top X customers in each year (which may not contain the same set of customers each year), and then present the sales contribution, either as a group or individually compared to the rest. Lorna gave a hint in the challenge that sets would help : “Your job is to figure out the best way to SET this up with the last 3 years dynamically”.

It took me a bit of a while to figure out how to make this work, and at the point of writing, haven’t looked at the solution to know if there was a better way. I ultimately ended up creating 3 sets to fulfil this challenge.

Setting up the parameters

This challenge requires 3 parameters

pTop

This identifies how many ‘top’ customers we want to consider. Defined as an integer from 10 to 100, defaulted to 20, that increments every 10 units

pShowCustomers

Determine whether the top customers’ contributions are displayed individually or as a group. Defined as a boolean, defaulted to False, and aliased to Yes or No

pPercentofTotal

Indicate whether the information is displayed as a % of total sales for that year, or as absolute sales values. Defined as a boolean, defaulted to True, and aliased to Yes or No.

Defining the core calculations

The requirement states to be able to determine the last 3 years ‘dynamically’. For this I created

Max Date

{FIXED: MAX([Order Date])}

to return the maximum Order Date in the whole data set.

We want to be able to restrict the data to the last 3 years, so create

Records to Show

DATEDIFF(‘year’, [Order Date], [Max Date]) <=2

I need to create a set for each of the 3 cohorts – the top customers for the latest year, the top for the previous year and the top for the year before that. For this I first need to determine the Sales for each of those timeframes.

The sales for the current year

Sales – CY

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

The sales for the previous year

Sales – PY

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

and the sales for the previous previous year

Sales – PPY

IF YEAR([Order Date]) = YEAR([Max Date])-2 THEN [Sales] END

I can then create the sets of customer I need (right click on Customer ID > Create > Set)

Customer ID Set – CY

get the Top number of records using the pTop parameter, and based on the sum of the Sales – CY field

Repeat the same process to create

Customer ID Set – PY

get the Top number of records using the pTop parameter, and based on the sum of the Sales – PY field

and

Customer ID Set – PPY

get the Top number of records using the pTop parameter, and based on the sum of the Sales – PPY field

To verify/understand what we’ve created, on a new sheet

  • Add Customer ID to Rows
  • Add Order Date to Columns at the Year level as a discrete (blue) pill
  • Add Records to Show to Filter and set to True.
  • Add Sales to Text.
  • Sort by the 2024 Sales value descending.
  • Add Customer ID Set – CY to Rows.

You should see the first 20 rows (assuming you haven’t changed the pTop value, display as In

If you now change the sort to sort by 2023 Sales descending, and swap the Customer ID Set – CY with the Customer ID Set – PY, you’ll get the same

So now that’s understood, we want to tag each of our customers based on the year of the order, whether they’re in the top n or not, and whether we want to display the customers individually or not

Group – Detail

IF (YEAR([Order Date]) = YEAR([Max Date]) AND [Customer ID Set – CY]) THEN
IF [pShowCustomers] THEN [Customer ID]
ELSE ‘Top N’
END
ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-1 AND [Customer ID Set – PY]) THEN
IF [pShowCustomers] THEN [Customer ID]
ELSE ‘Top N’
END
ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-2 AND [Customer ID Set – PPY]) THEN
IF [pShowCustomers] THEN [Customer ID]
ELSE ‘Top N’
END
ELSE
‘Other’
END

We’re also going to want to count customers, so need

Count Customers

COUNTD([Customer ID])

On a new sheet add Order Date at the year level as a discrete (blue) to Rows and add Group Detail to Rows too. Add Records to Display to Filter and set to True. Add Sales and Count Customers into the table. Show the pTop and pShowCustomers parameters

When pShowCustomers is set to No, you should just see 2 groupings per year

When set to Yes, you’ll get the Customer IDs listed

Note – the Sales numbers should reconcile to the solution – the count might not, which I believe is due to the solution counting distinct Customer Names rather than Customer ID.

To finalise the core calculations we need to build the initial viz, we have a different display depending whether we’re displaying the absolute or % Sales values.

Create

Sales % Total

SUM([Sales]) / TOTAL(SUM([Sales]))

format to decimal to 2 dp and add into table, adjusting the table calculation so it is computing by the Group – Detail only, so the percentage per year is being displayed.

Then we need

Measure to Plot

IF [pPercentofTotal] THEN [Sales % Total]
ELSE SUM([Sales])
END

format this to a number to 2 dp (just so you can see it has a value) and add to the table, applying the same table calculation settings. Display the pPercentofTotal parameter and flip between to see the column change.

Building the Viz

On a new sheet, add Records to Show to filter and set to True. Add Order Date at the year level as a discrete (blue) pill to Rows. Add Group – Detail to Detail. Change the mark type to bar. Add Measure to Plot to Columns and adjust the table calculation, so it’s computing just by Group-Detail.

Ste the sheet to fit width and show the 3 parameters.

Create a new field

Group – Top N

IF (YEAR([Order Date]) = YEAR([Max Date]) AND [Customer ID Set – CY]) THEN ‘Top N’
ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-1 AND [Customer ID Set – PY]) THEN ‘Top N’
ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-2 AND [Customer ID Set – PPY]) THEN ‘Top N’
ELSE ‘Other’
END

and add to Colour, adjusting the colours to suit. You’ll then need to update the table calculation of the Measure to Plot field to ensure Group – Top N is also checked.

We need to display labels, but these need to differ based what measure we’re showing, and the format is different, so create

Label – % Total

IF [pPercentofTotal] THEN [Sales % Total] END

format this to % with 1 dp and

Label – Sales

IF NOT([pPercentofTotal]) THEN [Sales] END

format this $ K to 1 dp.

Add both of these to the Label shelf and ensure they are listed directly side by side. Only 1 will ever actually display.

Change the pShowCustomers to Yes, and then add a white border via the Colour shelf. Add a Sort to the Group – Detail pill to sort by Sales ascending.

Add Sales, Sales % Total and Count Customers to the Tooltip shelf. additionally create

Tooltip – Customer

IF [pShowCustomers] AND [Group – Detail] <> ‘Other’ THEN [Customer Name] END

and add this to Tooltip too. Adjust the Tooltip to suit (make sure Sales % Total) is computing by both Group – Top N and Group – Detail so has the correct numbers.

Finally, hide the axis (uncheck show header on the Measure to Plot pill) and hide the Order Date label (right click and hide field label for columns).

Then add the sheet to a dashboard, and arrange the parameters suitably.

My published viz is here.

Happy vizzin’!

Donna

Can you find sales per customer per first purchase cohort?

Donna ColesLeave a comment

Kyle set the #WOW2025 challenge this week, using a summarised data set based on Superstore that he included in the challenge page.

Building out the core data fields

I started this challenge, by building out the data in a tabular format, so I could verify the calculations I needed. I focused on the ‘month’ level to start with before tackling the ‘year’ level which had the added requirement of containing ‘complete years only’, which I did find a bit tricky. Anyway, to start we need to determine the number of months between the First Order Date and the Order Date.

Months from 1st Purchase

DATEDIFF(‘month’, [First Order Date], [Order Date])

and we also need to identify the number of customers

Move this pill into the Dimension section of the left hand pane (drag it up to the top section above the line)

Count Customers

COUNTD([Customer ID])

and calculate the sales per customer

Sales per Customer

SUM([Sales])/[Count Customers]

format this to $ with 0 dp. Also format Sales to $ with 0 dp.

On a new sheet, add First Order Date to Rows as an exact date dimension (blue pill). Add Months from 1st Purchase Date to Rows. Then add Count Customers, Sales and Sales per Customer into the view.

Add First Order Date to the Filter shelf and select the Month/Year format, then select the 4 months Kyle used – Jan 2021, May 2021, Jun 2022, Nov 2023). Show the filter on the sheet.

Building the Month Level Viz

With the information we have, we can build out the viz at the ‘month’ level of granularity.

On a new sheet, add Months from 1st Purchase Date to Columns and Sales per Customer to Rows. Add First Order Date to the Filter shelf at the Month/Year level and restrict to the relevant dates. Show the filter control. Add First Order Date to Colour and set to the Month/Year level as a discrete (blue) pill. Adjust the colours to suit. I used colours from a palette called CB_Paired I had installed.

Set Stacked Marks to Off (Analysis Menu > Stack Marks > Off).

Create another instance of First Order Date (right click the field and select Duplicate) to crate First Order Date (copy). Rename this First Order Date (for Size). Add to the Size shelf, and set it to be at the Month/Year level and discrete (blue) pill.

Adjust the Sort on the First Order Date (for Size) pill to be by Data source order Descending. This now makes January wider than November.

And then add First Order Date to the Detail shelf at the Month/Year level as a discrete (blue) pill. By default this pill is sorted ascending, and has the effect of moving January to the back and November to the front so all the bars (at least for the 0 entry) are visible. This is why we needed a duplicate instance of First Order Date and we needed it to be sorted in one direction to make the correct months at the front, and in another direction to get the required bar widths.

Add Sales and Count Customer to the Tooltip shelf and adjust the Tooltip as required.

Handling the ‘Year’ requirement

Firstly for this, we need a parameter to drive the change in visual.

pGrain

string parameter listing ‘month; and ‘year’ and defaulted to ‘year’

Our Columns is currently listing the Months from 1st Purchase. We need this to be reflective of the years from 1st purchase instead based on the parameter selected.

Years from 1st Purchase

FLOOR([Months from 1st Purchase]/12)

this rounds the calculation down to the nearest whole number.

Move this into the dimension section of the data pane, and add to Rows of our tabular display, so you can see what it’s doing.

Our X-Axis will then be based on either of these 2 fields

X-Axis

IF [pGrain] = ‘month’ THEN [Months from 1st Purchase]
ELSE [Years from 1st Purchase]+1
END

Add this into the table, and show the pGrain parameter and see the change as you flip the options.

We then need to handle the ‘incomplete year’ requirement. This did take some trial and error, and I’m not totally convinced what I’ve done will work in all scenarios, but it seems to match Kyle’s results for the spot checks I did…

I started by determining the maximum month from 1st purchase in each cohort.

Max months per cohort

{FIXED [First Order Date]: MAX([Months from 1st Purchase])}

Add this into the table as a discrete dimension (blue pill), and each row should match the final Months from 1st Purchase value for each First Order Date

I then created a field to identify which of the rows in the data I wanted to keep. So in the case of the above examples for 01/11/2023, I want the rows where Months from 1st Purchase is from 0 to 11 as these represent a whole year’s worth of data for Years from 1st Purchase = 0 (even though there isn’t a a purchase every month in the year). I ended up doing this with the following calculation

Identify Cut Off

[Months from 1st Purchase] < 12 * (FLOOR(([Max months per cohort]+1)/12))

so based on the above example for 01/11/2023, get me all the rows where Months from 1st Purchase is less than 12 * (FLOOR((13+1)/12) = 12 * (FLOOR(14/12)) = 12 *1 = 12.

Add this into the table

I then created an additional field to filter by

Records to Display

[pGrain] = ‘month’ OR [Identify Cut Off]

and added this to the Filter shelf and set to True.

When we’re at the ‘month’ level, all rows will be included, otherwise, just those we identified will be.

So now we have this, we can adjust the visual (you may choose to take a copy of what was initially built, just in case).

Adjusting the Viz for the Granularity parameter

Show the pGrain parameter on the viz sheet and set to Year. Add Records to Display to the Filter shelf and set to True. Add X-Axis to Columns and remove Months from 1st Purchase Date. Set sheet to Fir Width.

Test changing the parameter and altering the selected dates and verify the behaviour is as expected.

Finally tidy up the formatting – remove all gridlines; edit the axis and remove the title; hide the X-Axis label heading (right click > hide field labels for columns). Add to a dashboard and arrange the colour legend in a single row, and set the First Purchase Date filter to be a multi-value dropdown.

My published viz is here.

Happy vizzin’!

Donna