Tag: Week 30

Can you build a ladder chart?

Donna ColesLeave a comment

It was my turn to set the #WOW2026 Week 30 challenge, and I based it on a viz I started building some time ago, but never finalised. Given Wimbledon has recently finished, I thought it was the perfect opportunity to get this one over the line. There are multiple components to this, so I split the challenge into ‘Levels’ to help people focus on what to work on, if time was limited.

Defining the core calcs

The data provided contains 1 row per match Sinner has played in, and each match is associated to a tournament. While typically matches in a tournament are played over several days, in the dataset the Date field is the same for all matches in a single tournament, so can be used a a unique identifier for a tournament.

The ladder chart is essentially a bump chart where each round in a tournament is ‘ranked’ based on how far into the tournament the round is, where the Final is the highest rank ie 1. So the first step is to assign a ‘rank’/position to each round.

Round Position

CASE [Round]
WHEN ‘F’ THEN 1
WHEN ‘SF’ THEN 2
WHEN ‘QF’ THEN 3
WHEN ‘R16’ THEN 4
WHEN ‘R32’ THEN 5
WHEN ‘R64’ THEN 6
WHEN ‘R128’ THEN 7
WHEN ‘Q3’ THEN 8
WHEN ‘Q2’ THEN 9
WHEN ‘Q1’ THEN 10
WHEN ‘RR’ THEN 11
END

We then need to be able to identify the best round achieved in the tournament

Best Round per Date

{FIXED [Date]: MIN([Round Position])}

We also need to identify the Grand Slam tournaments from other tournaments. I’ve used a set for this. Right click on Tournament > create > set

Is Grand Slam

select the 4 entries : Australian Open, Roland Garros, Wimbledon, US Open

I’ve also created a parameter

pGrandSlamsOnly

boolean parameter, defaulted to True, with the True option aliased as Grand Slams Only and False aliased as All Tournaments

I then created

Filter – Matches

([pGrandSlamsOnly] AND [Is Grand Slam]) OR NOT([pGrandSlamsOnly])

This will return true if either pGrandSlamsOnly = Grand Slams Only (ie is True) AND the tournament is a member of the Is Grand Slam set, or pGrandSlamsOnly = All Tournaments (ie is False)

To ensure only the best round per tournament is shown, I also created

Keep Best Round Only

[Round Position] = [Best Round per Date]

Finally, we need to identify if Sinner was the winner of the match or not. We can determine this by looking for the name Sinner in the text of the Match field which precedes the string ‘d. ‘ . (I assume d. stands for ‘defeated’).

Is Winner

CONTAINS(TRIM(SPLIT([Match], “d.”, 1 )),”Sinner”)

Building the ladder chart

On a new sheet, add Round to Filter and exclude RR. Add Keep Best Round Only to Filter and set to True. Add Filter – Matches to Filter and set to True. Then finally add Surface to Filter and select all options.

Add Date to Columns as a continuous (greed) exact date, and add Round Position as a continuous dimension (unaggregated green pill) to Rows. Edit the y-axis and reverse the axis. Change the mark type to a line and set the path to be a stepped line.

Add Tournament, Rank, Date, Surface and Round to the Tooltip shelf. Adjust the number format of the Rank field so it is a number with 0dp but prefixed with #. Adjust the format of the Date field on the Tooltip to be mmmm yyyy (ie August 2019 format).

Additionally, create a field

Label:Unranked

IIF(ISNULL([Rank]),’Unranked’,NULL)

and add this to Tooltip too, the adjust tooltip as required. Reduce the Size of the line and adjust the colour (I used #7ed0d9). Set the background colour of the worksheet too (I used #f7fcf5)

Add another instance of Round Position to the Rows to make a 2nd marks card. Set the mark type to Shape and add Is Winner to Shape. Increase the mark size. Set shapes accordingly. Add Surface to Colour and adjust to suit (I used colours from the hue circle palette, reduced to 60% opacity). Then make the chart dual axis and synchronise axis.

Add Is Winner to the Size shelf of the 2nd marks card, and adjust sizes to suit too, so the Star is at least as big as the other mark.

Remove all row/column dividers, gridlines, zero lines, axis lines etc.

To make the labelled ‘ladder rungs’, we’re simply adding multiple constant reference lines. Right click the left hand y-axis > add reference line. Add a constant at 10, labelled Q1 with a blue dotted line.

Then format the label so it is aligned in the middle of the line (I reduced font to 8pt) and removed any shading

Then repeat 9 more times for each ‘rung’ 🙂

At the end, hide both the axis (uncheck show header) and remove the x-axis title. Fix the x-axis from 01 Aug 2017 to 01 Jun 2027. The test the filters work as expected.

Building the Viz in Tooltip

On a new sheet, add Date as a discrete exact date (blue pill), Round, Match to Rows. Go back to the Ladder chart and set the Round, Filter – Matches and Surface filters to apply to this worksheet too. Apply a sort to the Round pill, to sort by the Round Position field.

Add Score to the Text shelf. Then double click into Columns and type the text ‘Result’, then right click the ‘Result’ text and hide labels for columns.

Go back to the Ladder chart and update the Tooltip – insert the VIT sheet, and adjust the size /width as required and apply filters to Date & Tournament.

On hover, the list of matches should now display

Building the KPIs

We need a few fields for this

Tournaments

COUNTD([Date])

Matches

COUNT([Sinner])

Note – this is just referring to the automatic sheet row count field

Wins

IIF([Is Winner?],1,0)

Titles

IIF([Is Winner?] AND [Keep Best Round Only],1,0)

Grand Slams

IIF([Is Grand Slam] AND [Is Winner?] AND [Keep Best Round Only],1,0)

On a new sheet, Add Measure Names to Columns and Measure Values to Rows. Remove the measures that aren’t required. Apply the same ‘shared’ filters to this sheet.

Change the mark type to a shape and use a transparent shape. Add Measure Names to Label and arrange labels middle centre, and format as required. Set the worksheet background to match the ladder viz.

Building the strip plot

Create a new field

Within Top 10

[Rank]<=10

On a new sheet, add this to Filter and set to True. Then apply the other 3 shared filters to this sheet.

Add Date to Columns as a continuous exact date (green pill). Fix the axis to the same dates as before 01 Aug 2017 to 01 Jun 2027. Set the Mark type to Circle and add Rank to colour, reversing the range as required so the lower number is darker. Set the circle to have a border and 80% opacity. Add Date, Surface, Tournament and Rank to Tooltip and adjust as required. Remove all gridlines, row/column dividers, gridlines etc and set background colour as before.

The double click into Rows and type MIN(0) to create a y-axis. Then add a 0 constant reference line (as before) but label this Within Top 10. The hide both the x and y-axes.

Building the dashboard

Now you have all the components, they just need to be added to a dashboard. Use layout containers to help with this. To ensure the ladder chart & the strip plot align, arrange these in a vertical container. Both objects should be set to fit entire view and any padding to the left or right of the objects should be identical.

The item hierarchy of my dashboard is below.

My published viz is here.

Happy vizzin’!

Donna

Can you highlight a treemap?

Donna ColesLeave a comment

This week, Sean decided to revisit a challenge from 2017, week 12, which was originally posted by Emma Whyte, one of the #WorkoutWednesday founding coaches.

I’ve been completing the #WOW challenges since their inception, so had the original solution already published to my Tableau Public.

Back then, parameter actions didn’t exist, so I decided to build this latest version using them instead of the parameter dropdown list included in the original requirement.

Building the basic viz

Create a new parameter to capture the Sub-Category we want to highlight

pSubCat

string parameter defaulted to ‘Bookcases’.

(NOTE – if I wanted to use a drop down for the user selection, I would instead have set this parameter to be a list populated from the Sub-Category field when the workbook opens).

I can’t always recall quickly the positioning of all the fields I need to build a treemap, so I started by simply double clicking the fields I needed in turn : Category, Sub-Category, Sales to add them onto the canvas, and then selecting the TreeMap icon in the Show Me tab to reposition the fields as required.

Then move the Category field from Text to Detail.

Colouring the blocks

The requirement is to show the selected Sub-Category in one colour, but also show a graduated colour palette for the non selected Sub-Categories.

First, let’s identify the selected Sub-Category.

Show the pSubCat parameter on the canvas. Then create

Is Selected Sub Cat

[Sub-Category] = [pSubCat]

Change the Sales pill on the Colour shelf from continuous (green) to discrete (blue). This will result in a rainbow of colours

Then add Is Selected Sub Cat to the Detail shelf. Then click on the icon next to the pill that indicates it’s on the detail shelf, and change it to Colour, so 2 fields are now on the Colour shelf.

Move the Is Selected Sub Cat field on the colour shelf so it is listed above the Sales field on the colour shelf. The selected sub-Category should now be highlighted, and the other blocks are graduated.

However, the highlighted sub-category is ‘separated’ from the Category block it belongs in. To resolve this, change the Is Selected Sub Cat field on the colour shelf so it is an Attribute. By setting this, the treemap is now only dividing itself by the Dimension fields of Category and Sub-Category.

Format the Sales field to $ with 0dp, and update the Tooltip as required.

Create the sheet title

Create a new fields

Selected Sales

{FIXED:SUM(IF [Is Selected Sub Cat] THEN [Sales] END)}

format to $ with 0dp and add to the Detail shelf.

Update the title of the sheet to reference the pSubCat parameter and the Selected Sales field and format as desired.

Add the interactivity

Add the sheet to a dashboard ,then add a dashboard parameter action

Set Sub Cat

On select of the treemap sheet on the dashboard, set the pSubCat parameter, passing in the value from the Sub-Category field. When the selection is cleared, keep the current value

However, when the treemap is clicked, the selected block gets ‘highlighted’ and the rest fade. To prevent this, create a new field

HL

‘dummy’

and add to the Detail shelf of the Treemap sheet. Then create a new dashboard Highlight action

Deselect

On select of the Treemap sheet on the dashboard , target the same sheet with the HL field only

As all marks have this HL value set, this has the effect of actually highlighting all marks ‘on click’ rather than just the actual one clicked, so making it look like nothing is actually highlighted.

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

Happy vizzin’!

Donna

Connected Scatterplots

Donna ColesLeave a comment

Sean set this challenge this week, to build a connected scatterplot to allow additional insights to be gained.

We need to show a circle per country related to the specified year, and then show the data for all years if a country is specified from the drop down, or ‘clicked on’ by the user; these data points are all then connected.

Let’s start by building up the various parameters and calculated fields needed to help with this.

Setting up the data

For the user inputs, I used parameters

pYear

Integer parameter, defaulted to 2000, displayed in a format so no thousand separators are shown. I populated the list using the values from the Year field.

pCountry

string parameter defaulted to All. I populated the list of entries by first adding values from the Country field. I then manually added an All entry to the bottom of the list and dragged it to the top. I could then set All as the default value.

On a new sheet, show these parameters.

We’re going to use a dual axis chart to display the viz, and for this, we’re going to get the relevant measures for the specific Year and for the specific Country.

To see what I’m aiming for, lets’ build out the data in a table. Add Country to Columns and Year to Rows. Display the values of Fertility Rate and Life Expectancy. This just gives us all the data points

But we only want the points related to the pYear (2000) or if pCountry if it’s not All (in this case Afghanistan).

So we create

Fertility Rate for Year

[Year] = [pYear] THEN [Fertility Rate] END

Life Expectancy for Year

IF [Year] = [pYear] THEN [Life Expectancy] END

format these to 1 dp and then add to the table. The fields only contain values for the specified pYear.

Create

Fertility Rate for Country

IF [Country] = [pCountry] THEN [Fertility Rate] END

Life Expectancy for Country

IF [Country] = [pCountry] THEN [Life Expectancy] END

format these to 1 dp and also add to the table. We now have these entries only existing for the selected pCountry.

If pCountry is set to All, the Fertility Rate for Country and Life Expectancy for Country are empty for every Country.

We now have the basics we need to build the viz.

Building the ScatterPlot

On a new sheet, show the parameters, then add Fertility Rate for Year to Columns and Life Expectancy for Year to Rows and Country to Detail. Change the mark type to circle. Adjust the Tooltip.

Create a new field

All Countries

[pCountry] = ‘All’

and add to the Colour shelf. Adjust the colours so when pCountry = All, the All Countries colour legend is True and displays a darker instance of a colour, as opposed to when pCountry is set to something else, and the All Countries colour legend is false.

Now add Fertility Rate for Country to Columns and Life Expectancy for Country to Rows. Change both fields to be Dimensions. On the 2nd marks card, add Year to Detail. and remove the All Countries field from the Colour shelf. Change the mark type to line and move Year to Path. Set the colour accordingly.

Then set both the Rows and Columns to be Dual Axis and synchronise both axis. Remove Measure Names from the colour shelf on the all marks card.

Adjust the Tooltip of the 2nd (line) marks card.

Add Year to the Label shelf of the 2nd marks card and update so it just displays for the Min & Max value. Adjust font size and style.

On the first marks card (the circle) add Country to Label and adjust so it only displays when selected.

Hide the right and top axis. Remove row & column dividers. Hide the null indicator and update the title of the axes. Name the sheet Scatterplot or similar.

Building the Dashboard

Add the sheet to a dashboard, and float the parameters into a suitable location. Add a floating text box that references the pYear parameter and position bottom left of the chart. Add a parameter action to update the pCountry parameter when a circle is clicked.

Click Country

On select of the Scatterplot Viz, set the pCountry parameter, passing in the value from the Country field. When cleared, set the parameter back to All.

Finally, if you click a circle to select a Country, you’ll find that the circles ‘fade out’ more than what you want – you want them to look the same as the colour when a country is selected via the dropdown. Essentially, you want all the circles to be ‘highlighted’ on click. To do this, create a new field

HL

“Highlight”

and add this to the Detail shelf on the scatterplot sheet. Then on the dashboard, add a Highlight action

HL marks on click

On select of the scatterplot viz, target itself, highlighting the HL selected field

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

Happy vizzin’!

Donna

Can you build a dynamic drill down scatterplot in one view?

Donna Coles1 Comment

Retro month continues, with Kyle setting this challenge to recreate Andy Kriebel’s WOW challenge from 2017. A lot has moved on with the product since 2017 and this is a great example of how it can be simplified.

I completed the original challenge (see here) and was having a look to refresh myself… boy! it took a LOT more effort – sometimes I surprise myself that I managed it!

Now that we have parameters and parameter actions, the solution is WAY more simpler.

So let’s crack on…

As alluded to above, we’re going to need a parameter which is going to store the name of the state ‘on click’

pSelectedState

string parameter defaulted to ” (ie empty string)

We also need to display either the name of a State or a City dependent on the value of this parameter

Display Name

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

Pop these into a tabular view with Sales and Profit and show the pSelectedState parameter so we can test things out.

When the pSelectedState is empty, a row is displayed per State

but when pSelectedState contains the name of a State (or any text to be honest), a row is displayed per City (note all Cities are displayed, at this point, not just those for the State).

To restrict the list of Cities just to those that match the State in the pSelectedState parameter, we need

Records to Filter

[pSelectedState] = ” OR [pSelectedState] = [State]

Add this to the Filter shelf and set to True. Now the list should be restricted to the Cities in the State.

So lets’ start to build the basic viz.

Set the pSelectedState parameter to empty, then add Sales to Columns, Profit to Rows and Display Name to Text. Add Records to Filter To Filter and set to True. Change the mark type to Circle.

Create a new field

Profit Ratio

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

format to % with 1 dp and then add this to the Colour shelf.

Add this sheet to a dashboard, then add a dashboard parameter action

Set State

on select of a mark on the scatter plot chart, set the pSelectedState parameter with the value from the Display Name field.

If we now click on a state, the cities should be displayed instead – great! But if we now click a city, we don’t get what we want – boo! This is because the selection of a City has passed the name of the City which is stored in the Display Name field into the parameter, so the scatter is trying to display records relating to a State = City Name which doesn’t exist.

To resolve this, we need to pass a different field into the parameter action

Drill Value

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

Add this to the Detail shelf of the Scatter plot viz, then update the dashboard action to pass this field into the pSelectedState parameter instead

Reset the pSelectedState parameter to empty string, and then test again – clicking on a state and then clicking on a city should get you back to the states.

And that’s the core functionality achieved with 1 parameter, and 2 calculated fields!

We just need some additional fields to provide the relevant display for the title & sub title

Title text

IF [pSelectedState] = ” THEN ‘by State’ ELSE ‘for ‘ + [pSelectedState] END

Subtitle

IF [pSelectedState] = ” THEN ‘Click a State to drill down to City level’
ELSE ‘Click a City to drill up to State level’
END

Add these to the Detail shelf of the scatter viz, and then update the title of the sheet to reference the fields

Update the Tooltip and adjust the size of the axis fonts, and tidy up the dashboard layout, and you should be good to go!. My published viz is here.

Happy vizzin’!

Donna

Dynamic Dates in a Heat Map

Donna ColesLeave a comment

Erica set the challenge this week using a custom made dataset. The focus was to handle viewing different aggregations of the dates based on the date range selected, ie for short date ranges seeing the data at a day level, for medium ranges, view at the week level and then at a monthly level for larger ranges. This type of challenge has been set before, but the visual is typically a line chart rather than a heat map. The principles are similar though.

Determining the Date to Display

Each row in the data set has a Date associated to it. For this challenge we need to ‘truncate’ the date to the appropriate level depending on the date range and the date level selected by the user. By that I mean that if the level is deemed to be weekly, we need to ‘reset’ each date to be the 1st day of the week.

To start with, we need to set up some parameters which drives the initial user input.

pStart

Date parameter defaulted to 11/07/2022 (11th July 2022), with a display format of <weekday>, <date>

pEnd

As above, but defaulted to 24/07/2022 (24th July 2022).

pDateLevel

integer with values 0-3 as listed below, with a text Display As. Defaulted to ‘Default’. Note, I am using integers as we will be referencing them in IF statements later, and integers are more performant than comparing strings.

With these parameters, we can then build

Date To Display

DATE(
IF [pDateLevel] = 0 THEN
IF DATEDIFF(‘day’, [pStart], [pEnd]) < 28 THEN DATETRUNC(‘day’,[Date])
ELSEIF DATEDIFF(‘day’, [pStart], [pEnd]) < 90 THEN DATETRUNC(‘week’, [Date])
ELSE DATETRUNC(‘month’, [Date])
END
ELSEIF [pDateLevel] = 1 THEN DATETRUNC(‘day’,[Date])
ELSEIF [pDateLevel] = 2 THEN DATETRUNC(‘week’,[Date])
ELSE DATETRUNC(‘month’, [Date])
END
)

If the pDateLevel is set to 0 (ie Default), then compare the difference between the dates entered and truncate to the ‘day’ level if the difference is less than 28 days, the ‘week’ level if the difference is less than 90 days, else truncate to the month level (which will return 1st of the month). Otherwise, if the pDateLevel is 1 (ie Day), truncate to the day level, if it’s 2 (ie Week), truncate to the week level, else use the month level.

To see how this field is working, add Date and Date To Display to Rows, both as discrete exact dates (blue pills), display the parameter fields, and adjust the values. Below you can see that using the Default level, between 1st May and 24th July, the 1st day of the associated week against each date is displayed.

Building the Bar Chart

This is the simpler of the two charts to build, so we’ll start with this.

Add Employee Name to Rows, and Minutes to Columns. and sort by Minutes descending (just click the sort descending button in the toolbar). Add Project to Filter, select ‘A’ and then show the filter. Adjust the colour of the bars. I used #311b92

We need to restrict the data displayed based on the date range defined.

Dates to Include

[Date]>=[pStart] AND [Date]<=[pEnd]

Add this to the Filter shelf and set to True.

Now, we need to label the bars, and need new fields for this.

Duration (hrs)

FLOOR(SUM([Minutes])/60)

Using the FLOOR function means the value is always rounded down to the relevant whole number (eg 60.5 and above will still result in 60).

Format this field to be 0 decimal places with a ‘h’ suffix

Duration (mins)

SUM([Minutes])%60

Modulo (%) 60 means return the remainder when divided by 60. Format this to be 0 dp.

Add both these fields to the Label shelf, and adjust so the labels are displaying on the same line and are aligned to the left. You may need to increase the width of each row to see them.

Add Project to the Tooltip shelf and update, then remove all gridlines and the axis. Leave the Employee Name column visible for now. We’ll come back to this later.

Building the Heat Map

On a new sheet, add Employee Name to Rows and Date to Display as a discrete exact date (blue pill) to Columns. Add Minutes to Colour and a heat map should automatically display.

Go back to the bar sheet, and set both the Project and the Dates To Include filters to apply to the heat map worksheet as well (right click each pill on the filter shelf -> Apply to Worksheets -> Selected Worksheets and select the other one you’re building).

Then click the sort descending button in the toolbar, to order the data as per the bar.

Add both Duration (hrs) and Duration (mins) to the Label shelf and change the mark type to Square. Adjust the label so it is on the same line and left aligned (I added a couple of spaces to the front of the text so it isn’t so squashed).

Edit the colours of the heat map – I used a colour palette I had installed called Material Design Deep Purple Seq which seemed to match, but may not be installed by default.

Format the main heat map section to have a light grey background by right clicking on the table -> format and setting the shading of the pane only to pale grey

Then adjust the row dividers of the pane to be white, and the header to be pale grey (set the level to the max).

Adjust the column dividers similarly, setting the pane to be white, the header to none, and level to max

Next we want to deal with the column labels. By default they’re showing the date, but we want this to show something different depending on the date level being displayed.

Column Label

IF (([pDateLevel] = 1) OR (([pDateLevel] = 0) AND (DATEDIFF(‘day’, [pStart],[pEnd])<28))) THEN UPPER(LEFT([Weekday],3))
ELSEIF (([pDateLevel] = 2) OR (([pDateLevel] = 0) AND (DATEDIFF(‘day’, [pStart],[pEnd])<90))) THEN ‘w/c ‘ + IIF(LEN(STR(DATEPART(‘day’, [Date To Display])))=1,’0’+STR(DATEPART(‘day’, [Date To Display])), STR(DATEPART(‘day’, [Date To Display]))) + ‘-‘ + UPPER(LEFT(DATENAME(‘month’, [Date To Display]),3))
ELSE UPPER(LEFT(DATENAME(‘month’, [Date To Display]),3))
END

If the date level is ‘day’ or the date level is the default and the start & end are less than 28 days apart, then show the day of the week (1st 3 letters only in upper case).

Else if the date level is ‘week’ or the date level is the default and the start & end are less than 90 days apart, then show the text ‘w/c’ along with the day number (which should be 01 to 09 if the day is < 10) a dash (-) and then the 1st 3 letters of the month in upper case.

Else, we’re in monthly mode, so show the 1st 3 letters of the month in upper case.

Add this to the Columns shelf, then hide the Date To Display field (uncheck show header), hide field label for columns and hide field label for rows. Format the Employee Name field so its a different font (I changed to Tableau Book).

Again play around with the parameters and see the changes to the column label.

Finally, add Project to the Tooltip and update. You’ll need to adjust the formatting of the Date To Display field to get it into <day of week>, <date> format.

Adding to the Dashboard

When you add the two charts to the dashboard, you’ll need to set them side by side within a horizontal layout container. Remove the titles. They both need to be set to ‘fit entire view’, and the width of the heat map chart should be fixed (I set it to 870px), so it retains the space it stays in, even when you only have 1 month displayed.

Once you’re happy the order of the heat map employees matches the order of your bar chart, uncheck show header against the Employee Name field on the bar chart.

To get the bars to align with the heat map, I showed the title of the bar, removed the text and just entered a space. This dropped the bars so they just about aligned. You may need to tweak by increasing the size of the bars slightly.

Finally, you need to move your parameters around. I placed them in a horizontal container, whose background colour was set to pale grey. I then set the objects within to be equally spaced by setting the container to Distribute Contents Evenly.

I then altered the padding of each of the parameter objects to have outer padding = 0 and inner padding = 5 and added a pale grey border surrounding each.

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

Happy vizzin’!

Donna

Let’s go streaking!

Donna ColesLeave a comment
It was Sean Miller’s turn to set the challenge this week, where the primary focus was to find the highest number of consecutive months where the monthly sales value was higher than the previous month.

This was a table calculations based challenge, and I always tackle these by building out the data required in a tabular format. The challenge was also reminiscent of a previous challenge Sean has set, which I’ve blogged about here, and admit I used as a reference myself.

So let’s get started.

To start with, we need the month date, the Sub-Category, the Sales value and the difference in Sales from the previous month. For the month date, I like to define this explicitly

Order Date Month

DATE(DATETRUNC(‘month’,[Order Date]))

This aligns all Order Dates to the 1st of the relevant month.

Add Sales Category, Order Date Month (set to discrete exact date blue pill), and Sales into a view, then set a Quick Table Calculation of Difference on the Sales pill

Edit the table calculation to compute by Order Date Month only, so the previous calculation restarts at each Sub-Category.

Then drag this pill from the marks card into the left hand data pane to ‘bake’ the calculated field into the data model. Name the field Sales Diff. The re-add Sales back into the view too, so you can double check the figures.

Identify whether there is an increase with the field

Diff is +ve

IF [Sales Diff]>0 THEN 1 ELSE 0 END

Add this into the view too, and verify the calculation is computing by Order Date Month only again.

Now we need to work out if the row matches the previous value

Match Prev Value

LOOKUP([Diff Is +ve],-1) = [Diff Is +ve]

The LOOKUP is looking at the previous row (identified by the -1) and comparing to the current. If they match then it returns True else False.

Again add into the view, and again double check the table calc settings. In this case there is nested calculations so you need to double check the settings against each calc referenced in the drop down

Now we need to work out when there are consecutive increases, and how many of them there are

Increase Streak

IF (NOT([Match Prev Value])) AND ([Diff Is +ve] = 1) THEN 1
ELSEIF [Diff Is +ve] = 1 THEN ([Diff Is +ve]+PREVIOUS_VALUE([Diff Is +ve]))

END

If the current row has a +ve difference and the previous row wasn’t +ve, then we’re at the start of an increase streak, so set to 1. Else, if the current row has a +ve difference then we must be on a consecutive increase, so add to the previous row, and this becomes a recursive calculation, so builds up the values..

Add this onto the view, set the table calc settings, and you can see how this is working…

So now we’ve identified the streaks in each Sub-Category, we just want the maximum value.

Longest Streak

WINDOW_MAX([Increase Streak])

Add this and set the table calc setting again. You’ll see the max value is spread across every row per Sub-Category.

Finally we need to identify Sales values in the months when the streak is at its highest.

Sales of Month with Longest Streak

IF [Longest Streak]=[Increase Streak] THEN SUM([Sales]) END

Add this into the view again (don’t forget those table calc settings), and you’ll notice that for some Sub-Categorys there are multiple points with the same max streak

With all this we can now build the viz, which is relatively straight forward….

Add Order Date Month (exact date, continuous green pill) to Columns, Sub-Category to Rows and Sales to Rows. Edit the Sales axis to be independent, then change the line type of the Path to stepped

Add Sales of Month with Longest Streak to Rows and set to dual axis, and synchronise. Make sure the mark type of the 2nd axis is set to circle, and remove Measure Names from the colour shelf of both marks.

Manually set the colour of the line chart to grey. Add Longest Streak to the Colour shelf of the circle marks card. Adjust the colour to use the green palette, set to stepped of 5 value and ensure the range starts at 0 and ends at 5 (don’t forget to edit the table calc settings!).

Now add Longest Streak as a discrete blue pill to the view too.

This is all the core components. The last thing we need to do is sort the list. I wasn’t entirely sure how it had been sorted, apart from the largest Longest Streak at the top. I created a new field for this

Sort

[Longest Streak]*-1

and added this as a blue discrete pill in front of Sub-Category….

…, then hid the column.

Then just apply the tooltip and relevant formatting on the chart.

For the legend, I created a new field

Legend

CASE [Sub-Category]
WHEN ‘Art’ THEN 0
WHEN ‘Chairs’ THEN 1
WHEN ‘Labels’ THEN 2
WHEN ‘Paper’ THEN 3
WHEN ‘Phones’ THEN 4
ELSE 5 END

and added this into a new sheet as below

The components then just need to be added to the dashboard. My published version is here.

Happy vizzin’! Stay Safe!

Donna

Can you create a drill down using set actions?

Donna ColesLeave a comment

For week 30, #WorkoutWednesday alumni Emma Whyte returned re-posting this challenge which was originally set in Week 41 of 2017 (see here). The idea behind this was to see how the challenge could be achieved using features that have been released since that challenge – in this case set actions.

I’ve been doing the #WorkoutWednesday challenges since they were first introduced, so I completed the original challenge, which is posted here.

Despite it being over 2 1/2 years ago, I had a strong recollection as to what was required to achieve this. So the challenge I set myself, was to recreate without looking at my own solution.

Building out the data

This is one of those challenges where we can build the data out into a table to check the functionality before building the actual viz. I always like to do this where possible, as I find it a good reference to make sure I’m getting the logic & the calculated fields I need right.

Start by adding State & City to Rows and add Sales & Profit via Measure Names on Columns .

As the challenge is to use Set Actions, then naturally, we’re going to need a Set. The Set we need is based on State with the idea being that when there is a State(s) in the set, then the City will display instead.

Selected State

Right click on State and Create -> Set. Select an option in the dialog, eg Alabama say

We will need to show the marks based on State or City depending on whether a State has been selected or not. We need a single field that we will use in the viz that displays the dimension we need to show

Display Value

IF [Selected State] THEN [City] ELSE [State] END

Add this onto the Rows and you’ll see how this is working

We can test the functionality of putting values into and out of the set without the need for the dashboard action at this point, by right-clicking on Selected State and selecting Show Set – the list of set values to select will display (a bit like a filter list).

We need a way to figure out what rows to show – how to identify whether there’s anything selected in the set.

Count States Selected

{FIXED : COUNTD(IF [Selected State] THEN [State] END)}

By being an LOD, this will set the count of the items in the set across all the rows in the data. Add to the sheet so you can see how this works

So we want to show information when either there isn’t anything in the set, or for the rows associated to the Selected States only

Records to Show

[Count States Selected] = 0 OR [Selected State]

Add this to Rows and test out… with no State in the set, all the rows are True

but with a State selected, only the rows associated to that State are True

But we seem to have too many marks showing when there’s nothing in the set….?

That’s fine.. just take City out of the view now, and if you deselect all States you should get the 48 rows we’re going to start with listed, and all are Records to Show = True. The Sales & Profit values will also now be aggregated to the appropriate level.

Building the Viz

Ensure your Selected States set is empty, and build out the scatter plot

  • Profit on Columns
  • Sales on Rows
  • Display Value on Detail & Label
  • Records to Show on Filter set to True
  • Mark Type = Shape set to x

Verify the functionality by clicking a State in the list, and the view should change to show the City.

We need to colour the marks based on Profit

-ve Profit?

SUM([Profit])<0

Add this to the Colour shelf and adjust colours accordingly.

Finally we need to look at how the title/subtitle changes based on which level we’re at.

Title

IF [Count States Selected] = 0 THEN ‘Sales vs. Profit by State’
ELSE ‘Sales vs. Profit for ‘ + [State]
END

Subtitle

IF [Count States Selected] = 0 THEN ‘Select a state to drill down to city level’
ELSE ‘Double-click a city to drill up to state level’
END

Add these onto the Detail shelf, then they’ll be available to reference in the Title of the sheet.

And then adjust the Tooltip, and we’re pretty much ready to go.

Adding the Set Action

Create a dashboard and add the scatter plot sheet to it.

Add a dashboard action to Change Set Values which runs on the Select action, and assigns values to the Selected State. On clearing the selection, values are removed from the set.

And that should pretty much be it. My published version is here. I thoroughly enjoyed the ‘throwback’ to previous challenges, and would like to see this theme continue on occasion.

Happy vizzin’! Stay Safe

Donna