Tag: Highlight Actions

Can you use Image Role & Dynamic Zone Visibility?

Donna Coles2 Comments

New year means only one thing – a change to the WorkoutWednesday hashtag! We’re now #WOW2023, and it was Lorna’s turn to kick the next year of challenges off with a focus on the latest Tableau features. This challenge uses the image role function introduced in 2022.4 and the dynamic role visibility feature introduced in 2022.3 (so you need to make sure you’re using at least 2022.4 to complete this).

Setting up the data source

Lorna provided an excel based data sheet which I downloaded. It contains several tabs, but the two you need to relate are Player Stats and Player Positions, relating as Lorna describes on both the Game ID and the Player Name

The data needs to be filtered to just the men’s competition, so I did this by adding a data source filter (right click on the data source -> Edit Data Source Filter) where Comp = Rugby League World Cup. Doing this meant I didn’t have to worry about adding fields to the Filter shelf at all.

Building the scatter plot

For the basic chart, simply add All Run Metres to Columns and Passes to Rows, then add Game ID and Name to the Detail shelf.

We need to identify which player and game is selected when a user clicks, so we need parameters to capture these.

pSelectedGameID

integer parameter defaulted to 0

pSelectedPlayer

string parameter defaulted to nothing/empty string/ ”

Show these parameters on the canvas, and then manually enter Brandon Smith and 21.

We now need calculated fields to reference the parameter values

Is Selected Player

[Name] = [pSelectedPlayer]

Is Selected Game

[Game ID] = [pSelectedGameID]

Both return a boolean True / False value, and we can then determine which combination of player/game each mark represents, which we need in order to colour the marks.

Colour : Scatter

IF [Is Selected Game] AND [Is Selected Player] THEN ‘Both’
ELSEIF [Is Selected Player] THEN ‘Player’
ELSEIF [Is Selected Game] THEN ‘Game’
ELSE ‘Neither’
END

Add this field to the Colour shelf, change the mark type to Circle and adjust the colours accordingly. Manually sort the order of the values so that the light grey ‘Neither’ option is listed last (which means it will always be underneath any other mark).

While not stated in the requirements, the marks for the selected player are larger than the others, so add Is Selected Player to the Size shelf, and adjust the size range to suit.

Adjust the tooltip and name the sheet Scatter.

Building the Bar Chart

On a new sheet, add Player Image and Game ID to Rows and All Run Metres and Passes to Columns. Add Is Selected Player to Filter and set to True. Edit the title of the sheet so it references the pSelectedPlayer parameter, and align centre.

Set the Player Image field to use the actual image of the player stored at the URL, by clicking on the ABC datatype icon to the left of the field in the data pane, and selecting Image Role -> URL

This will change the data type icon and update the view to show the actual player image

Add Is Selected Game to the Colour shelf on the All marks card and adjust accordingly. Remove the tooltips.

We need to show the axis titles at the top, rather than bottom. To do this add another instance of the All Run Metres field to the right of the existing one. Make dual axis and synchronise axis. Repeat with the Passes field by adding another instance to the right of the existing one and making dual axis again.

Set the mark type on the All marks card back to bar.

Right click on the All Run Metres bottom axis, remove the title, and set the tick marks to none. Repeat for the Passes bottom axis.

Right click on the All Run Metres top axis, and just set the tick marks to none. Again repeat for the Passes top axis.

Adjust the font of both axis title (right click axis -> format; I just set to Tableau Book) and then manually decrease the height of the axis.

Finally click the Label button on the All marks card and check the Show mark labels check box.

Then remove all column and row divider lines.

Adding the interactivity

On a dashboard, add a Vertical Layout Container, then add the bar chart into it and then add the scatter plot underneath. Remove the container that contains all the legends and parameters – we don’t need to show any of these. Hide the title of the scatter plot.

Add a dashboard parameter action to set the pSelectedPlayer parameter on select of a circle on the scatter plot. Set this parameter to pass the Name field into the parameter, and when clearing the selection, set the value to empty string/ nothing / ”

Select Player

Add another similar dashboard parameter action, which sets the pSelectedGame parameter in a similar way. This time, the Game ID field is passed into the parameter which is then set to 0 when the selection is cleared.

Select Game

If you test clicking on the scatter plot, you should now see the bar chart data disappear (although some white space about the height of the title remains) and you’ll also see that the circle selected is ‘highlighted’ compared to all the others.

To resolve the highlighting issue, navigate back to the scatter plot sheet, create a new calculated field called Dummy which just contains the string ‘Dummy’. Add this field to the Detail shelf.

Navigate back to the dashboard, and add a dashboard highlight action, that on select on the scatter plot, targets the same sheet on the same dashboard, but only focus on the Dummy field.

Deselect Marks

To make the whole section where the player bar chart is displayed, completely collapse, we need another parameter

pShowPlayerDetail

boolean parameter defaulted to false

We also need a boolean calculated field called

Show Player Detail

TRUE

Add this to the Detail shelf of the Scatter plot sheet.

Then navigate back to the dashboard, and create another parameter dashboard action, which on select of a circle on the scatter plot, sets the pShowPlayerDetail parameter based on the value from the Show Player Detail field. When the selection is cleared, reset the parameter to False.

Show Player Detail

Finally select the bar chart on the dashboard, then click the Layout tab on the left and side, and tick the Control visibility using value check box, and in the drop down select Parameters – > pShowPlayerDetail.

Now when a circle is clicked on, the bar chart section will completely disappear and the scatter plot will fill up the whole space.

Finalise the dashboard by adding the title and legend. My published viz is here.

Happy vizzin’!

Donna

Can you do YoY comparisons?

Donna ColesLeave a comment

Community month for the #WOW2022 team continued this week, with Liam Huffman setting this challenge to build a year on year comparison chart with twist.

Typically when building YoY charts you compare the month from last year with the same month this year, or a week from last year with this year (eg week 3 2021 v week 3 2022), or a specific date last year with this year (eg 4th March 2021 with 4th March 2022). However for this challenge, the focus was on being able to compare based on equivalent weekdays (at least that’s what I understood from reading). By this I mean if 10 October 2022 is a Monday, that needs to be compared with the equivalent weekday in the previous year. 10 October 2021 was a Sunday, so we actually need to compare Monday 10 Oct 2022 with Monday 11 Oct 2021.

So that’s the direction I took with this challenge, but in doing so couldn’t get the numbers to exactly match with Liam’s solution. Unfortunately I just couldn’t get my head around Liam’s approach even after looking at it. So I’m blogging this based on my interpretation of the requirement, which may be flawed.

Baselining the Dates

When building YoY charts like this which need to be flexible based on the date part selected, you need to ‘baseline’ the dates; well that’s the term I use, others might refer to it as normalising. Ultimately you need to get the dates that span multiple years to all align to the same year, so you have a single x-axis that just spans a single year.

Now based on what I talked about above, its not just a case of changing the year of every Order Date to match for all the records, we need to find the equivalent weekday from the previous year and align that.

To do this I first created a parameter

pToday

date parameter defaulted to 10 Oct 2022

This is simply to provide a constant end point. In a business scenario when the data changes, any reference to pToday would just use TODAY().

I then created

1st Day of Current Year

DATE(DATETRUNC(‘year’, [pToday]))

This returns 1st Jan 2002

I then worked out what weekday this was

Weekday of 1st Day Current Year

//determine the day of the week this year started on, based on todays date
DATENAME(‘weekday’, [1st Day of Current Year])

This returns Saturday

I then wanted to find the date of the first Saturday in every year, but firstly I needed to determine the weekday for each Order Date

Weekday

DATENAME(‘weekday’, [Order Date])

Date of 1st weekday Per Year

//get the first date in each year which falls on the same weekday as the first day of the current year
//ie 1st Jan 2022 is a Saturday. This is day 0 for 2022. We need to find day 0 for every other
//year, which is the date of the first Saturday in that year

{FIXED YEAR([Order Date]): MIN(IF [Weekday]=[Weekday of 1st Day Current Year] THEN [Order Date] END)}

For each year, get all the dates which fall on a Saturday, then returned the earliest one of those.

Popping all this information out in a table, we can see that in 2019, the first Saturday in the year was 5th Jan 2019, so all the records for dates in 2019 are stamped with 5th Jan 2019.

This Date of 1st weekday Per Year is essentially day 0 for each year. We now need to record a number against each day in the year

Days From Date

//get the number of days from the date of 1st weekday per year
DATEDIFF(‘day’, [Date of 1st weekday Per Year], [Order Date])

And with this number, we can now record the equivalent date in 2022 against each date

Baseline Date

//need to normalise all dates for every year to the current year,
//ie Day 0 for every year = 01 Jan 2022

DATE(DATEADD(‘day’, [Days from Date], [1st Day of Current Year]))

Add these fields into the tabular view and you should hopefully see how this is working

Building the Line Chart

We need some additional parameters to help build the chart.

pDatePart

string parameter defaulted to Month, with 3 values listed as shown below

pStartDate

date parameter set to use the 1st Day of Current Year when the workbook opened, meaning current value was 1st Jan 2022

pEndDate

date parameter set to use the pToday parameter when the workbook opened, meaning current value was 10th Oct 2022

With the pDatePart field, we need to define the actual date we’re going to plot

Date to Plot

DATE(DATETRUNC([pDatePart], [Baseline Date]))

when this is set to week, the date for all days in the same week are set to the first date in the week. Similarly, when this is set to month, all dates in the same month are set to 1st of the month.

The data also needs to be filtered based on the start & end dates selected, so we need

Dates to Include

[Baseline Date]>= [pStartDate] AND [Baseline Date]<=[pEndDate]

Add this to the Filter shelf of the tabular view and set to True. Also add Date to Plot into the table, and show all the parameters. Change the dates and the date part field and familiarise yourself with how the parameters impact the data.

Now we’re happy we’ve got all the key fields we need, then create a new sheet, show the parameters and add Dates to Include = True to Filter.

Then add Date To Plot as an exact date continuous (green) pill to Columns and Sales to Rows.

Create

Order Date Year

YEAR([Order Date])

and add to Colour. Adjust the colours accordingly.

Format the Sales axis, so the numbers are displayed as numbers rolled up to thousands (k) with 0 dp. Edit the Date to Plot axis to remove the title, then format the dates displayed to be in dd mmm custom format. Remove all gridlines and zero lines. Only axis lines should be displayed. Update the title.

Add Date to Plot to Tooltip as a discrete attribute (blue pill). Add MIN(Order Date) to Tooltip too. Adjust tooltip wording to suit.

Building the bar chart

Add Order Date Year to Rows and Sales to Columns. Sort Order Date Year descending.

Add Order Date Year to Colour. Adjust the opacity to about 50% and add a border. Widen the rows. Add Sales to Label and format label to be $K to 1 dp.

Add another instance of Sales to Detail, then adjust to use a Quick Table Calculation of Percent Difference. Move this pill from Detail to Label. Adjust the table calculation so it is relative to Next rather than previous

Format the Sales field that has the table calc, so it is custom formatted to ↑ 0.0%; ↓ 0.0%

Modify the label so the % change is in ( ) .

Add MIN(0) to Columns (type directly in to the columns shelf). Remove the two Sales fields from the marks card, and add another instance of Order Date Year to the Label shelf. Adjust the Label so the font is larger, matches mark colour and is rotated.

Make the chart dual axis, and synchronise axis.

Turn off all tooltips, hide the Order Date Year column and both axis. Remove all gridlines and row & column borders. Add a title. Remove any instance of Measure Names that may have been added to the Colour shelf of either marks card.

And add Dates To Include = true to the Filter shelf.

Adding the interactivity

Add the sheets onto a dashboard and adjust the layout to match. I floated the parameters and positioned with some floating text boxes too, to get the desired display.

Add a highlight dashboard action

Highlight Trend

which on hover of the bar, highlights the trend line via the Order Date Year field only.

To manage the filtering of the bars, I decided to use a parameter action, by passing the date related to the point selected. For this I created

pDateHovered

I used a string parameter, so I had a value I could use to reset to

I then needed to create an additional field

Date to Plot String

STR([Date to Plot])

and I added this to the Detail shelf on the trend sheet. This needs to be set to be an attribute, so the lines remained joined up.

Additionally I needed

Filter On Hover

[pDateHovered] = ” OR [Date to Plot] = DATE([pDateHovered])

which I added to the Filter shelf of the Bar chart sheet and set to true.

Then back to the dashboard, create a parameter action

Filter Bars

which on hover on the trend chart, updates the pDateHovered parameter passing through the Date To Plot String field, and resets back to <empty string> when released.

And that should be it… my published viz is here.

Happy vizzin’!

Donna

How much do top sub-categories contribute to sales?

Donna ColesLeave a comment

A colourful #WorkoutWednesday challenge this week, courtesy of Ann Jackson incorporating pie charts, top N functionality and interactivity and a highlight table. Pie charts can cause much debate amongst the data viz community and if this one was just representing the multitude of sub-categories, it certainly wouldn’t be ideal. But when the core aim is to simply present 2 key measures (those in the top N against the rest), the pie is a familiar and effective visual. In this instance, the outer ring segmenting all the sub-categories provides additional context without detracting from the main purpose of the viz.

So lets build…

  • Creating the core calculations
  • Building the Pie Chart
  • Building the Highlight Table
  • Adding the Interactivity

Creating the core calculations

First up, we’re going to need a parameter to define the ‘Top N’. Create an integer parameter with a range from 1 to 17, that steps every 1 interval, and is defaulted to 5.

pTopN

Next we’re going to use a Set to capture the Sub-Categories that are in the Top N Sales. Right click on Sub-Category -> Create ->Set. Use the Top tab to define a set captures the Sum of Sales that is based on the pTopN parameter.

Now, we want to create a grouping of those in and out of the set, which will be used as part of the highlight table

Sub-Cat Group

IF [Sub-Category Set] THEN ‘IN TOP ‘ + STR([pTopN])
ELSE ‘ALL ELSE’
END

Pop all these fields out into a table so you can see what’s going on as you change the pTopN parameter. Sort the Sub-Category by Sales descending.

Now we need to identify the % value of Sales for the Sub-Categories that are in the Top N (this is the label on the darker segment of the central pie chart), so for that we need

Total Sales

{FIXED:SUM([Sales])}

Top N Sales (in hindsight, this should have been named Sales per Group or similar)

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

Top N Sales %

IF ATTR([Sub-Category Set]) THEN
SUM([Top N Sales])/SUM([Total Sales])
END

Format this to percentage with 0 dp.

Adding to the table, we can see the values

The final field we need in order to build the pie, is an additional one to store the label text

Label:SubText

IF [Sub-Category Set] THEN ‘TOP ‘ + STR([pTopN]) END

Building the Pie Chart

To achieve this we’re going to build a dual axis pie chart, where one pie is used to define the In/Out of Top N segmentation in the centre, and the other pie is used to create the outer ring.

Create an axis by typing in MIN(0) onto the Rows shelf, and then adding another instance of MIN(0) next to it. This will generate 2 marks cards, which is where the fields to build the pie charts will be placed.

In the first MIN(0) marks card, change the mark type to Pie, then add Top N Sales to the Angle shelf and Sub-Category Set to the Colour shelf. Adjust colours to suit. Then add Top N Sales % and Label:SubText to the Label shelf. Adjust size of the view and the chart to suit. Also remove all text from the Tooltip.

Positioning the text is a bit fiddly. If you click on the text so the cursor changes to a cross symbol, you can then drag it to a better location. However, when you change the Top N parameter, the text will move. You can go through each parameter value and reposition the text each time (which I did.. it wasn’t too onerous for 17 values), however I found when published to Tableau Public, the positioning wasn’t honoured. Ann’s solution was the same, so I didn’t get too hung up on this, although if anyone resolved it, I’d love to know!

Now on the 2nd MIN(0) marks card, again change the mark type to Pie, and this time add Sales to the Angle shelf and Sub-Category to Colour. Sort the Sub-Category field by Sales descending. Additionally add Sub-Category Set to the Detail shelf (this will be needed later on to make the interactivity work). Edit the colour palette to use the Hue Circle options. Adjust the size of the pie chart. Adjust the tooltip too.

Now make the chart dual axis and synchronize the axis. If the colourful chart is displayed ‘on top’, then right click on the right hand axis and select move marks to back. Adjust the sizes of both pies, so the colour wheel is slightly larger than the other one.

Now hide the axis, and remove all borders and gridlines.

Building the Highlight Table

I’ve built the highlight table as a bar chart. Start off by adding Sub-Category Set, Sub-Cat Group and Sub-Category to Rows. Sort Sub-Category by Sales descending. Then type in MIN(1) into the Columns shelf.

Now add subtotals via the Analysis > Totals > Add all Subtotals menu. This adds 2 additional rows to each section

But we don’t want the ‘grand total’, so click on the Sub-Category Set context menu, and uncheck Subtotals

To position the totals at the top, go to Analysis > Totals > Column Totals To Top

Then add Sub-Category to the Colour shelf, and adjust the colour of the Total bar to white

We now need to get some text onto those bars, but we need some additional calculations to help up with this. Firstly, we want to get the rank of the Sub-Category. We’ll use a table calculation for this

Sales Rank

RANK(SUM([Sales]))

We also need a way to identify the Total rows differently from the main Sub-Categories. I referred to this Tableau KB for help here, and subsequently created

Size

SIZE()

To see what this is doing, add Size to the Label shelf, and adjust the table calculation setting to compute by all fields except the Sub-Category Set. The size of the total rows is 1.

Based on this logic, we can then create

LABEL:Bar

IF [SIZE]=1 THEN ‘SUBTOTAL FOR GROUP’
ELSE ‘#’+STR([Sales Rank]) + ‘ ‘ + ATTR([Sub-Category])
END

Add this to the Label shelf instead of the Size field and adjust the table calc settings as above. Align left. Then add Sales to the Label shelf too and adjust so its on the same row. Adjust the tooltip too.

Now hide the Sub-Category Set and the Sub-Category fields. Right click on the ‘IN TOP x’ text and Rotate Label, then click on Sub-Cat Group text and Hide Field Labels for Rows. Format the header text to suit.

Hide the MIN(1) axis, and set columns and gridlines to None. Adjust the Row dividers to be darker

Adding the Interactivity

Add the 2 sheets onto a dashboard, and add a Highlight Dashboard Action, that on Hover of either of the charts, it highlights the other chart based on the Sub-Category Set only.

I think that’s covered everything. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Visualise Our Survey Data

Donna Coles1 Comment

This week, Ann Jackson set a table calculations based challenge, using the responses from a recent survey on #WorkoutWednesday, as the most requested topic was for table calcs!

There’s a lot of visuals going on in this challenge, and I’m shortly off on my holibobs (so will be playing catch up in a couple of weeks), so I’m going to try to pare down this write up and attempt just to focus on key points for each chart.

Donut Chart

By default when you connect, Respondent is likely to be listed in the ‘Measures’ part of the data pane – towards the bottom. This needs to be dragged into the top half to turn it into a dimension. You can then create

# of Respondents

COUNTD([Respondent])

which is the key field measures are based on throughout this dashboard.

When building donuts, we need to get a handle on the % of total respondents for each track, along with the inverse – the % of total non-respondents for each track. To do this I created fields

Total # of Respondents

TOTAL(COUNTD([Respondent]))

and then

Track – % of Total

[# of Respondents]/([Total # of Respondents])

along with the ‘inverse’ of

Non Track – % of Total

1-[Track – % of Total]

To then build the donut, we ultimately need to create a dual axis chart, with Which track do you participate in? on Columns and a MIN(1) field on Rows. Manually reorder the entries so the tracks are listed in the relevant order.

On the first MIN(1) axis/marks card, build a pie chart. Add Measure Names to the filter shelf and filter to the Track & Non Track % of Total fields. Set Mark Type to Pie Chart and add Measure Values to the angle shelf. Add both Which track do you participate in? and Measure Names to the Colour shelf. Set a white border on the Colour shelf. Reorder the entries in the colour legend, and set the colours appropriately.

The create another MIN(1) field next to the existing one on the Rows shelf

Set this marks type to circle, and remove all the fields from the colour & detail shelves. Set the colour to white. Add Which track do you participate in? and Track – % of Total to the Label shelf and format. Reduce the Size. Make dual axis, and synchronise. Further adjust sizes to suit.

Participation Bar Chart

Plot How often do you participate? against # of Respondents, and then add a Quick Table Calculation to the measure using Percent of Total. Manually re-sort the order of the entries, show mark labels and Colour the bars light grey. Apply relevant formatting.

Diverging Bar Chart

In this bar chart, the percentage of ‘agree’ responses are plotted to the right on the +ve scale and the percentage of the ‘disagree’ responses are plotted to the left on the -ve scale. The percentage of the ‘inbetweeners’ (neither agree nor disagree) is halved, and displayed on both sides. To address this, I created the following:

# of Respondents – Diverging +ve

CASE ATTR([Answer])
WHEN ‘Agree’ THEN [# of Respondents]
WHEN ‘Strongly Agree’ THEN [# of Respondents]
WHEN ‘Neither Agree nor Disagree’ THEN [# of Respondents]/2
END / [Total # of Respondents]

This is the % of total respondents for the ‘agree’ responses and half of the ‘inbetweeners’.

Similarly I then have

# of Respondents – Diverging -ve

(CASE ATTR([Answer])
WHEN ‘Disagree’ THEN -1*[# of Respondents]
WHEN ‘Strongly Disagree’ THEN -1 *[# of Respondents]
WHEN ‘Neither Agree nor Disagree’ THEN ([# of Respondents]/2) * -1
END) / [Total # of Respondents]

which is doing similar for the ‘disagree’ responses, except all results are multiple by -1 to make it negative.

The Question field is added to the Filter shelf and the relevant 5 questions are selected. Answer is also on the Filter shelf with the N/A answer excluded.

Add Question to Rows (and manually sort the entries), then add # of Respondents – Diverging -ve and # of Respondents – Diverging +ve to Columns and add Answer to the Colour shelf. Manually resort the entries in the colour legend and adjust the colours accordingly.

Make the chart dual axis, and synchronise the axis. Change the mark type back to bar and remove Measure Names from the colour shelf if it was added. Edit the bottom axis to fix the range from -0.99 to 0.99 and amend the title. Format the axis to display as percentage to 0 dp. Hide the top axis.

Additionally format both the measures to be percentage 0dp, but for the # of Respondents – Diverging -ve custom format, so the negative value is displayed as positive on the tooltip.

Adjust formatting to set row banding, remove gridlines etc and set tooltips.

Vertical bar chart

The best way to start building this chart is to duplicate the diverging one. Then remove both measures from the Columns shelf and add Answer to Columns. Manually re-sort the answers. Add # of Respondents to Rows and add a Quick Table Calculation of percent of total.

Show the marks label, and align bottom centre, and match mark colour. Hide the axis from displaying, and also hide the Question field (uncheck show header). Update the Tooltip.

Heatmap

Right click on the Question field > Aliases and set the alias for the relevant questions

Also add Question to Filter and select relevant values. Add Answer to Filter too and exclude NULL.

Add Question to Columns and add Answer to the Text shelf. Add # of Respondents to the Text shelf, and set to Percent of Total quick table calculation. Edit the table calculation to compute using the Answer field only

We need to get each of these columns ‘sorted’ from high to low – we want to rank them. To do this, add # of Respondents to Rows, then change it to be a blue discrete pill. Add a Rank quick table calculation and once again set to compute by Answer only. Also set the rank to be Unique

Now change the mark type to square, and then add the # of Respondents percent of total field onto Colour as well as Text (the easiest way to do this to retain all the table calc settings, is to hold down Ctrl then click and drag the pill from the Text shelf onto Colour. This should duplicate the pill.

Format the % of total displayed to be 0dp, and adjust the label. Change the Colour to use the purple range and set a white border too. Hide the ‘rank’ field from displaying and hide field labels for columns too.

The dashboard

I used a vertical container then added the objects as required, using nested horizontal containers to organise the side by side charts.

To make the diverging bar and vertical bar charts look like they are one chart, adjust the padding of diverging bar chart object to have 0 to the right, and similarly, adjust the padding of the vertical bar to have 0 padding to the left.

I found it a bit fiddly to get the charts to line up exactly. Both charts were set to fit entire view. The diverging bar chart displays it’s title. I also displayed a title on the vertical bar chart, but made the text white so it’s invisible.

Dashboard filter actions are set against the donut and the participation bar charts.

The filter uses selected fields, which for the donut chart references the Which track do you partcipate in? field. A similar dashboard action needs creating for the participation chart as the source and references the How often do you partcipate? field.

A highlight dashboard action is required for the diverging and vertical bar charts. They only impact each other and should be set up as below on hover.

Hopefully I’ve covered everything… my published version is here.

Happy vizzin’! Stay Safe!

Donna

Can you rebuild the Olympic Schedule?

Donna ColesLeave a comment

This week’s #WOW challenge was a joint one with the #PreppinData crew, with the intention to use the #PreppinData challenge to create the data set needed for the Tableau challenge. I completed the Prep challenge, but decided to use the output provided by the #PreppinData crew as the input to this challenge (just in case I had inadvertently ended up with discrepancies).

  • Sport Selector
  • Adjusting the time
  • The Schedule Viz
  • Event Counts in Tooltip
  • Event listing Viz in Tooltip
  • Other Sports bar chart Viz in Tooltip
  • Dashboard interactivity

Sport Selector

This is a simple chart that lists the Sport Groups. I chose to build a bar chart using MIN(1) on the Columns and Sport Group on Rows. The axis was then fixed from 0-1.

Create a set based on Sport Group and select a few values to be ‘in’ the set (eg Boxing, Gymnastics, Martial Arts).

Add the Sport Group Set to the Colour shelf to identify the selected sports. Adjust colours accordingly.

Adjusting the Time

Create a parameter pTimeAdjust which is an integer paramater, defaulted to 0 and ranges from -12 to +12. Set the step value to 1 as this will ensure when you add the parameter to the dashboard, the prev/next buttons can be displayed alongside the slider.

Create a calculated field to store the time of the event based on the ‘timezone’ selected via the above parameter

Date Time Adjust

DATEADD(‘hour’, [pTimeAdjust], [UK Date Time])

This field will be used to display the full event date & time on the event listing viz in tooltip, along with building the schedule viz itself.

Additionally, create a field based on the above, which just stores the day of the adjusted datetime field above

Day of Adjusted Date

DATE(DATETRUNC(‘day’,[Date Time Adjust]))

This field is needed to help with the filtering required for the viz in tooltips to display.

The Schedule Viz

Add Date Time Adjust set to the Month datepart (blue pill) to the Columns shelf, and alongside it add the same field set to the Day datepart (blue pill). On the Rows, add Sport Group and Sport. Add the Sport Group Set to the Filter shelf. This will give you the ‘bones’ of the schedule

In viewing the provided solution, there was a bit of a discrepancy between when a ‘medal’ icon should show or not, compared to the Medal Ceremony? field provided in the data. It transpired Lorna had made an adjustment, as there were some events that had a ‘final’, but did not include a gold medal or ceremony event.

So to try to match up with Lorna’s output, I too made adjustments, but I can’t guarantee it matches any published solution.

First up I identify the Victory Ceremony events

Is Victory Ceremony?

CONTAINS([Event],’Victory Ceremony’)

I chose to exclude all these events from the schedule, so this field is added to the Filter shelf and set to False.

I also identify the events which appear to be a ‘final’

Is Final?

CONTAINS([Event],’Gold Medal’) OR CONTAINS([Event],’ Final’)

This field will separate the events into two types. Change the Mark Type to Shape, then add this field onto that shelf. Set the shapes accordingly. Note – to add the medal shape, save the image Lorna provided to your machine, then follow these instructions so it’s available for selection.

I chose to add the Is Final? to the Size shelf too, so the shapes can be adjusted to something more suitable.

If you add the rows and columns dividers, you’ll notice the single circles aren’t centred. To resolve this, we’re going to need some axis.

Add MIN(1) to the Rows shelf (y-axis). This will give us some vertical headspace.

Now we need to manage the horizontal space, and ensure the marks don’t overlap each other. When there’s no finals, we want the circle to be plotted in the middle. When there’s both non-final and final ‘events’ we want the two marks to be off-centre, one to the left and one to the right.

We need some calculations to help with this.

#Events by Sport Per Day

{FIXED [Day of Adjusted Date], [Is Victory Ceremony?],[Sport]: COUNT([Event Schedule])}

This helps us count the number of of events per day for a specific sport

#Event Finals By Sport By Day

{FIXED [Day of Adjusted Date], [Is Victory Ceremony?],[Sport]: SUM(IIF([Is Final?],1,0))}

This basically helps us count the number of finals for each sport on a day.

With this we can build

X-Axis

IF [#Event Finals by Sport Per Day] =0 THEN 5
ELSEIF [#Event Finals by Sport Per Day]-[#Events by Sport Per Day] =0 THEN 5
ELSEIF [Is Final?] THEN 7
ELSE 3
END

If there’s no final, plot at 5, if there’s only a final, plot at 5 otherwise plot a final at 7 and a non-final at 3.

Add this to the Columns shelf (set to be a dimension ie not SUM), and edit the axis to be fixed from 0-10.

Events Count in Tooltip

I was also a bit puzzled by some of the numbers being displayed in the tooltip, so chose to compute and show the following 3 measures

  • Number of Events for Sport on that day (this is the #Events by Sport per Day already calculated)
  • Number of Event Finals for Sport on that day (this is the #Event Finals by Sport by Day already calculated)
  • Total Number of events for all other sports on that day (ie the selected sport is excluded from the count).

For this last measure we need

#Events per Day

{FIXED [Day of Adjusted Date], [Is Victory Ceremony?] : COUNT([Event Schedule])}

#Events per Day of Other Sports

SUM([# Events Per Day]) – SUM([#Events by Sport Per Day])

Pop all these on the Tooltip shelf and format appropriately.

You’ll also need to add the Day of Adjusted Date to the Tooltip. This should be set to exact date and discrete (blue pill).

Event Listing in Tooltip

Build out a data listing view of Sport Group, Sport, Date Time Adjust and add Event (I renamed the Event Split field) to the Text shelf. Add Day of Adjusted Date to the Detail shelf. Hide Sport Group and format.

On the schedule viz, add this worksheet to the tooltip, passing Sport and Day of Adjusted Date as filters on the string

Other Sports bar chart Viz in Tooltip

Once again this is a relatively simple chart to build out, with the Day of Adjusted Date field hidden in the display (but necessary for the VIT to filter properly).

However, this will display all sports, and we need this chart to not show the sport that has been initially selected (hovered).

Create a parameter pSportToExclude which is a string parameter. For the purpose of demonstration, enter the text Football.

Create a field

Excluded Sport?

[Sport]=[pSportToExclude]

Add this field to the Filter shelf and set to False, and the sport will disappear from the list

Add a reference to this sheet from the tooltip of the schedule viz, this time passing just Day of Adjusted Date as the filter.

Dashboard Interactivity

Hide / Show Sport Selector

When adding the Sport Selector sheet and the Schedule viz to the dashboard, you need to make sure they exist side by side in the same horizontal container.

Then, providing you are using v2021.2, you can set the Sport Selector object to Hide/Show. See this video for help.

Add remove sports

You will need 2 dashboard set actions for this. They should run on ‘menu’, and one will add items to the set, and the other remove

Set the selected sport to exclude

We’ll use a parameter action for this to run on hover and set the pSportToExclude parameter

Stop Sport Selector highlighting

Create a new field Dummy containing the text “Dummy” and add this to the Detail shelf of the Sport Selector viz.

The add a highlight action against this sheet only

Hopefully I’ve ticked off all the core elements here. There was a fair bit going on, and I’m conscious I’ve drafted this blog fairly quickly in comparison. My published viz is here .

Note, there are a couple of elements in my viz that I added which weren’t on the original solution. I’ve chosen not to include in the blog as the images/characters I chose to use didn’t render on Tableau Public. If you download the workbook, you’ll be able to see what my intention was.

I did also create an alternative view ‘heatmap’ style view as well which you can see here.

Happy vizzin’! Enjoy the Olympics! Stay Safe!

Donna

Is Profit Ratio Influenced by Quantity?

Donna ColesLeave a comment

For this week’s challenge, Ann asked we recreated a scatter plot that ‘on click’ became a connected scatter plot and also displayed an ‘insights’ box to the user.

Building out the data

The scatter plot is to show Profit Ratio by Quantity, so first up we need

Profit Ratio

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

formatted to percentage with 0 dp.

A mark is shown on the scatter plot per Category and Month/Year. When working with dates like this, I often prefer to created a dedicated field to use as an ‘exact date’ which I can then format. So I built

Month Year

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

and formatted it to the March 2001 format (ie mmmm yyyy).

We’re also going to need a set based on the Category field, as that is going to drive the interactivity to help us connect the dots ‘on click’. So right click on Category and Create Set and just tick one of the categories displayed for now.

Selected Category

Let’s build these out into a table as follows

  • Order Date to Filter, filtered by years 2018 & 2019
  • Category, Selected Category & Month Year (exact date , discrete) onto Rows
  • Measure Names to Text and Filter to Quantity & Profit Ratio
  • Right click on Selected Category and select Show Set to display a selection control to easily allow the set values to be changed

To help drive the interactivity, we’re going to need to store the value of the Profit Ratio for the Selected Category in the set.

Selected PR

IF ATTR([Selected Category]) THEN [Profit Ratio] END

Format this to percentage 0 dp too, and add this to the table – it will only display the profit ratio against the rows associated to the category in the set

At this point, we’ve got what we need to build out the scatter plot, but I’m going to continue to create the fields needed for the ‘Insights’ piece.

These are looking for the months that contained the Max & Min values for the Profit Ratio and Quantity. I’m going to tackle this with Table Calcs.

Max PR per Category

WINDOW_MAX([Profit Ratio])

Adding this to the table, and setting the table calculation to compute by Month Year only, gives us the Max Profit Ratio for each Category

We need 3 more fields like this :

Max Qty per Category

WINDOW_MAX(SUM([Quantity]))

Min PR per Category

WINDOW_MIN([Profit Ratio])

Min Qty per Category

WINDOW_MIN(SUM([Quantity]))

Add all these to the table, setting the table calc in the same way as described above.

Now we know what the min & max values are, we need to know the month when these occurred.

Max PR Month

WINDOW_MAX(IF ATTR([Selected Category]) AND [Profit Ratio] = [Max PR per Category] THEN MIN([Month Year]) END)

Format this to March 2001 format.

If the category is that selected, and the profit ratio matches the maximum value, then retrieve the Month Year value, and duplicate that value across all the rows (this is what the outer WINDOW_MAX does).

Add this to the table in the Rows, and adjust the table calc settings for both the nested calculations to compute by Month Year only. The month for the selected category which had the highest profit ratio will be displayed against all rows for that category, and null for all other rows.

Again we need similar fields for the other months

Max Qty Month

WINDOW_MAX(IF ATTR([Selected Category]) AND SUM([Quantity]) = [Max Qty per Category] THEN MIN([Month Year]) END)

Min PR Month

WINDOW_MAX(IF ATTR([Selected Category]) AND [Profit Ratio] = [Min PR per Category] THEN MIN([Month Year]) END)

Min Qty Month

WINDOW_MAX(IF ATTR([Selected Category]) AND SUM([Quantity]) = [Min Qty per Category] THEN MIN([Month Year]) END)

Format all these too, and add to the table with the appropriate table calc settings applied.

If you now change the value in the the Selected Category set, you should see the table update.

Building the Scatter Plot

On a new sheet, build out the basic scatter plot by

  • Order Date to Filter, filtered by years 2018 & 2019
  • Profit Ratio to Rows
  • Quantity to Columns
  • Category to Colour and adjust accordingly
  • Month Year exact date, continuous to Detail
  • Change Shape to filled circle
  • Adjust the Tooltip to suit
  • Change the axis titles to be capitalised
  • Change the sheet title
  • Remove all row/column borders & gridlines. Leave the zero lines and set the axis rulers to be a sold grey line

Add Selected PR to Rows, and change the mark type of that card to line, and add Month Year to path. In the image below, I still have Furniture selected in my set.

Add Month Year to the Label shelf too and format so the text is smaller and coloured to match mark colour

Make the chart dual axis, synchronise the axis and remove the Measure Names from the colour shelf of each mark. Hide the Selected PR axis.

If you now change the selected set value, you can see how the other categories ‘join up’.

Building the Insights Box

Take a duplicate of the data table built originally, and add Selected Category to Filter. If you’ve still got an entry in your set, this should reduce the rows to just those records for that Category.

Remove the fields Profit Ratio, Quantity, Selected PR from the Measure Values section, and remove Selected Category from Rows.

Create a new field

Index = Size

INDEX()=SIZE()

and add this to the Filter shelf, setting the value to true.

The INDEX() function will number each row, the SIZE() function will number each row based on the total number of rows displayed (ie all rows will display the same number), so INDEX()=SIZE() will return true for the last row only

Now we’ve only got 1 row displayed, we’ve got the info we need to build the data in the Insights box by

  • Move Month Year to Detail
  • Category to Colour
  • Move Max PR Month, Max Qty Month, Min PR Month, Min Qty Month to Text
  • Move Max PR per Category, Max Qty per Category, Min PR Per Category, Min Qty per Category to Text

This will probably show duplicate instances of the values. This is because we didn’t fix the table calculation setting of the Index=Size field. Edit that to compute by Month Year

and then re-edit the Index=Size filter to just be true again, and expand the display so you can see all the values.

Now you can easily format the Text to the required display

I used this site to get the bullet point image from

Building the dashboard & interactivity

Add the scatter plot to the dashboard, and remove the legends that automatically get added.

Float the Insights sheet and position bottom right, remove the title. fit entire view, and also remove the colour legend which will have been added.

Add a dashboard Set Action to assign values to the Selected Category set and remove all values when the selection is cleared

Additionally, add a highlight action that highlights the Category field only (this keeps the lines highlighted on selection of a single mark).

Now practice clicking on and off marks on the scatter plot and you should see your lines & insights box being added and removed.

Finally add the title and header line by adding a vertical container at the top above the scatter plot.

Add a text box for the title, and underneath add a blank object.

Set the padding of the blank object to 0 and the background to black. Then edit the height to 4, and adjust the height of the container if it’s now too large.

Make adjustments to the position of the floating insights box if need be so it isn’t overlapping any axis.

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

I’ll be taking a break for a couple of weeks, so will be on catch up when I return from some R&R.

Happy vizzin’! Stay Safe!

Donna

Can you dynamically display a label on a heat map?

Donna Coles4 Comments

Ivett Kovacs returned with the #WOW challenge for this week, providing 2 versions. She provided some hints in the requirements which helped. With a bit of head scratching I managed to deliver an advanced version too. It didn’t exactly match what Ivett had delivered, but I was quite happy with my version. Until my follow #WOW participant Rosario Guana pointed out that the display ‘on click’ of a row or column total didn’t work properly 😦

I’ve spent A LOT of time trying to figure out what’s required but can’t resolve it. I’ve checked out Ivett’s solution and noticed a slight flaw in her logic too, so I feel uncomfortable changing my flaw for another. I’ve also looked at other solutions, and all are showing slightly different behaviour when you click on a column or row cell total. As a result, I’ve decided to blog based on the Intermediate version only. Others have posted blogs about their Advanced versions, so I’m going to save some time today, and reference them instead.

Building the basic heat map

The core of this challenge is going to involve Sets, and we’re going to use Set Actions to identify which cell is being interacted with.

For the intermediate version, we’re interacting based on hover actions.

The heatmap displays a grid of months by years, and we need to build sets based off of these. So we’ll start by creating dedicated calculated fields storing these values

Month Order Date

DATENAME(‘month’,[Order Date])

Year Order Date

YEAR([Order Date])

Add Month Order Date to Columns and Year Order Date to Rows, then drag Quantity onto Colour and change to AVG. You should have your basic heatmap.

From the Analysis -> Totals menu, select to shoe row & column grand totals. They will initially display as white cells, so you need to Edit Colours on the colour legend and select Include Totals/

Let’s sort some formatting out at this point.

Set the mark type to Square

Format the borders by setting the row & column dividers to none, but setting the Grand Total pane & header to be thick, white lines

And change the word Grand Total, by right clicking -> format on each cell and changing the Grand Total label.

Adjust the size of the Month & Year labels and remove header labels from displaying and you have

Labelling the total cells

We need to permanently display the label on the total row/column, and only show the label on the cells ‘on hover’.

We need a way to identify when we’re on a total cell. Ivett gave a hint “What is the Size() of the totals and the cells?“. This is a trick I’ve read about and discussed here. Basically the size() of the grand total row or column will be 1, so this is what we’re going to base some logic on.

Size of Months

SIZE()

Size of Years

SIZE()

Total Label

IF [Size of Years]= 1 OR [Size of Months] = 1 THEN AVG([Quantity]) END

If we’re on a row or column grand total, then show the AVG(Quantity). Add this onto the Label shelf, and edit the table calculation so the Size of Years nested calc is computing by Year Order Date, and the Size of Months nested calc is computing by Month Order Date.

Labelling the central cells

We need to create some sets, as these are going to determine whether we are hovering on a cell or not. The dashboard action will add values to a set on hover.

Right click on Month Order Date and Create Set, selecting an arbitrary month.

Hover Month Set

Do the same with Year Order Date

Hover Year Set

We then need a field to label the cell

Cell Label

IF ATTR([Hover Month Set]) AND ATTR([Hover Year Set]) AND [Size of Months]>1 AND [Size of Years]>1 THEN AVG([Quantity])
END

If something has been selected in the Hover Month Set and something has been selected in the Hover Year Set, and we’re not on a grand total row/column, then show the AVG(Quantity).

Add this to the Label shelf, and edit the table calculation settings as above.

Tooltip

The tooltip displays the Profit Ratio

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

so you’ll need to create this field, add to the tooltip and adjust the display accordingly.

Add the interactivity

Add the above sheet onto a dashboard, then create a dashboard action to Change Set Values, which is set to

  • run on Hover
  • Target the Hover Month Set
  • Assign values to the set when run and remove values from the set when cleared

Repeat to create a set action to target the Hover Year Set.

Test it out – hover your mouse over the cells. If you hover on a total, you should get the values displayed for all the values in the row or column

Advanced Version

As I stated in the intro, I didn’t end up with something I was entirely happy with. While writing this up, I’ve tried to rebuild using ideas from other published solutions, but I still have got anything ‘perfect’.

Ivett’s solution is obviously the closest to what we’re aiming for, since that is the challenge. The minor flaw I found in that though was that in some of the labelling logic there’s a step which compares the ‘average of the selected cell’ with the ‘average of the current cell’, and if they match, display the AVG(Quantity), otherwise show the difference. This logic is basically trying to work out ‘if current cell = selected cell’, but it doesn’t fully work, as there are other cells where the values match – it’s subtle as the displayed values are faded out with the cell highlighting, as I’ve shown in the example below.

Kyle Yetter has blogged his solution here, but if you click on a row or a column total in his solution, the values are all the same, and not showing a difference.

Rosario Guana has blogged her solution here. Rosario has adapted her solution slightly and displays both the average values and the difference ‘on select’, so the logic I was struggling with, isn’t being used in the same way here.

No doubt with a bit more time, I might finally crack this one, but for now, I need to ‘put it to bed’ 🙂

Happy vizzin’! Stay safe!

Donna

Can you add and remove items from a set?

Donna ColesLeave a comment

Lorna set a fun ‘create your own pizza’ challenge this week to demonstrate the ability to both add and remove items in a set via the use of Set Actions, a feature introduced in v2020.2 (so you’re going to need this to complete the task).

There’s essentially 5 components to this dashboard, which I’ll guide you through

  • The central bar chart
  • The graphical product type selector on the left
  • The list of selected products to the right
  • The actual vs budget bar at the top
  • An indicator of how much you’re over/under budget

Central Bar Chart

The essence of this is a simple Type, Product by Price bar chart, coloured by Type. Manually move the Type field so the ‘Size’ option is at the top.

To indicate if the product is vegetarian or not, we create

Veg Indicator

IF [Vegetarian] = ‘Yes’ THEN ‘●’ ELSE ” END

which we can add to the Row shelf, and format to be coloured green (I use this site to get the shapes).

The tick is used to indicate if the product has been added to the pizza or not. Selected items will be identified by the use of a set, so we need to create one. Right-click Product and Create->Set. Tick a few options.

And similar to the Veg Indicator we can build

Selected Product Indicator

IF [Selected Products] THEN ‘✔’ ELSE ” END

Add this to the Row shelf between Type and Product.

The order of the Products listed will change. I want it to be ordered alphabetically by Product within each Type. The quickest way to resolve this, was to duplicate the Product field, add it to the Rows between the Type and Selected Product Indicator pills, and then hide it.

The final requirement for this chart, is to highlight the selected products in a different shade of the ‘base’ colour.

Add Selected Products to the Detail shelf, then change the … detail icon to the left of the pill to the colour icon. This will mean there are 2 pills on the Colour shelf, and the colour legend will change. Adjust to suit

The chart just then needs formatting to add the Price to the Label of the bar, remove the row/column lines, hide the Type column, hide the field labels, hide the axis, and adjust the width of the columns.

Product Type Selector

Lorna provided the images needed for this, which need to be saved to a new folder in the Shapes directory of your My Tableau Repository. I called my new folder simply Custom.

Then on a new sheet, add Type to Rows, set mark type to Shape and add Type to shape too.

Hide the Type heading and remove the row lines.

Selected Products List

Add Type & Product to Rows and enter MIN(1) onto the Columns. Add Selected Products to the Filter shelf to restrict the list just to those in the set.

Add Type to Colour and Product to Label, changing the font to white and centring. Fix the axis from 0-1 and hide it. Hide the Type & Product columns.

Manually reorder the Type field to be listed Size -> Crust -> Sauce -> Toppings

Actual vs Budget Bar

The budget field can be changed by the user, so we need a parameter, Budget, for this, which is an integer parameter where the display is formatted to £ with 0dp, and defaulted to 15.

Create the bar by adding Price to Columns, Selected Products to Filters and Type to Colour. Reorder the items in the colour legend to get the colours displaying in the correct order with ‘Size’ on the left and ‘Toppings’ on the right.

Add the Budget parameter to the Detail shelf, then add a Reference Line which refers to the Budget. Adjust the label displayed to be custom as shown below, and format the thickness and colour of the line to suit

Format the reference line so the text is displayed at the top right, and edit the font & size of the label displayed.

Then make the row height of the chart taller, but adjust the size of the bar to be narrower. The reference line spans the whole height of the row, so reducing the height of the bar itself provides space for the label to display without overlapping the bar.

Hide the axis and remove gridlines etc.

Finally we need to display the total price of the products selected. We can’t just add Price to Label as there are multiple segments on the bar, and this will display a Price per Type.

Instead we need a new field

Total Price Selected Products

{FIXED [Selected Products]:SUM([Price])}

Since this chart is filtered by Selected Products = True, this returns the total price of all the items in the set (selected). Add this to the Columns shelf and make dual axis & synchronise axis.

Readjust the mark types, so the Price marks card is back to a bar, and the Total Price Selected Products is Text. Remove the Measure Names pill from the Colour shelf of both cards (the dual axis will have automatically added it), and also remove the Type pill from the Colour shelf of the Total Price Selected Products card. Instead, add Total Price Selected Products to the Text shelf of this card.

The text will overlap the bar whether you align left , middle or right. The trick is to add some spaces in front of the text and then align right.

Hide the axis.

Over / Under Budget Indicator

This is just a text display, but a few calculated fields are needed so the text can be coloured differently depending on whether it’s over or under.

First we need to know the difference from budget

Diff from Budget

[Budget]-[Total Price Selected Products]

Text Budget Diff Over

IF [Diff From Budget] < 0 THEN ABS([Diff From Budget]) END

This will just display the difference if the value is negative, but the ABS function will return the number as a +ve. This field should be formatted to £ with 2 dp.

Text Budget Diff Under

IF [Diff From Budget] >= 0 THEN [Diff From Budget] END

This will just display the difference if the value is positive. Format to £, 2 dp.

Only one of these fields will ever hold a value at any time.

Similarly we need fields to show the text to display.

Over Budget Text

IF [Diff From Budget] < 0 THEN ‘Over Budget’ END

Under Budget Text

IF [Diff From Budget] >= 0 THEN ‘Under Budget’ END

On a new sheet, add Selected Products to the Filter shelf, then add all four of the above fields to the Text shelf.

Adjust the order and colour of the fields in the Label editor, setting the fields related to being ‘over budget’ to red and the ‘under budget’ fields to green.

Now we’ve got all the building blocks, we can put it all on a dashboard.

Building the dashboard interactivity

Add all the sheets in their various locations. Most were tiled, except for the Budget parameter and the Over/Under Budget Indicator sheet, which I floated.

Filter the bar chart

Add a dashboard Filter Action to filter from the Type Selector sheet to the Product Bar Chart on select, filtering on the Type field only.

Add / Remove from Set

Add a dashboard Set Action to the Chart sheet that runs from the Menu, and adds the selected item to the Selected Products set.

Create another Set Action on the same chart, which removes values instead.

And that’s the main crux of the challenge. The only addition is ‘nice’ feature to avoid the item selected from being shown as selected (ie fading out all the other items in the chart).

I reverted to the trusty true = false dashboard action which I applied to the Selector chart and the Selected Items chart.

This involves creating 2 new fields True = True and False = False and adding these to the Detail shelf of the relevant chart.

A dashboard Filter action is then added which targets itself using the fields True = False

However, when I tried to add this same feature to the main bar chart itself, the bar chart stopped working as expected. I think there was conflict between there being 2 filter actions on the same chart. I have to admit, this is where I did check Lorna’s solution, as this was a small feature that really bothered me, and I didn’t want to publish without it.

It turns out she simply used a highlight action to resolve this

I vaguely recall using this sometime ago, but the ‘true=false’ concept has become so ingrained as my ‘go to’ method, that I struggled to think of this.

And that should be it. You now have the tools to customise your own pizza 🙂

My published viz is here. Enjoy!

Happy vizzin’! Stay Safe!

Donna

Which Customers are Costing Us?

Donna Coles1 Comment

#WorkoutWednesday Week 41 saw Luke challenge us with this task, based on a real scenario he’d encountered during his job.

Before I’d even saw Luke’s tweet advertising the challenge, I knew this was going to require Set Actions

To avoid any discrepancies interpreting the requirements, Luke very kindly defined all the calculations, so I’m not going to dwell on these. This blog will focus on the requirements I think will be most useful / those I had to think about, so I’m making assumptions you can build the calcs and the basic vizzes required. My published workbook (referenced at the end of the blog) can be downloaded if you’re not too sure.

Only Show Customers with Sales over $500

I created a fixed level of detail (LoD) calculation to store the total sales per customer

Total Sales per Customer

{FIXED [Customer Name] : SUM([Sales])}

which I then referenced in another calculation

Sales > 500

[Total Customer Sales]>500

which I added to the Filter shelf of all the sheets I built, setting the value to True.

Change the circle colour on the scatter plot for selected customers

I created a Set based on the Customer Name

I called the Set Selected Customer and just ticked a random set of customers in the list presented (I’ll describe how the set members, ie the customers, change later).

Dragging the Selected Customer set onto the Colour shelf of my scatter plot, gives me an In/Out colour option, where the ‘In’ represents the customers that are members of the set (those I randomly selected above), and ‘Out’ is those customers that aren’t members.

Move the selected customers to the top of the table

In the table viz, add the Selected Customer set onto the Rows shelf, in front of the Customer Name. This will add a level of ‘grouping’ to the table, with those ‘In’ the set being listed above those ‘Out’ of the set.

Then untick Show Header against this pill, to hide the In/Out from the display

Show a totals for each cohort

This is achieved by adding subtotals to the table.

Go to Analysis > Totals > Add all Subtotals

This will add a Total row which can then be formatted via a right-click, where the Label can be renamed to ‘Cohort’ and bold text applied.

Change the colour of the ‘unselected’ customers to dark grey in the table

I have to admit, I did scratch my head on this one for a few moments…but then the light bulb switched on 🙂

This is simply an adjustment of the row banding formatting option, ensuring the band size & level are set appropriately

Sort Customers by Lost Sales

In the table, this is just done by applying a Sort to the Customer Name field, using the Lost Sales calculated field that you should already have created.

Change the members of the Selected Customer Set…

…which in turn will change which customers are displayed at the top of the table.

This is where Set Actions come into play.

Add both the scatter viz and the table viz to a dashboard.

Then go to Dashboard -> Actions ->Add Action > Change Set Values

Select the Scatter viz to be the source sheet, and the Target Set to be the Selected Customer set. Choose to run action on Select and opt to remove all values from the set when the selection is cleared.

Now test the set action, by selecting circles on the scatter plot – you should see your selections changing colour and the customers selected being listed at the top of the table…. we’re nearly there… one final tricky requirement…

Make sure all members remain un-highlighted on the scatterplot

What you’ll notice when testing the set action above, is that on selection, while your selected customer circles change colour, the unselected customer circles’fade out’

The challenge is for the unselected circles to remain the same colour as in the screenshot to the left above.

We need to use a highlight action to do this, but trick it in such a way that it doesn’t actually highlight anything…

I did this by creating a new calculated field called Dummy which I just set to be an empty string (“”).

Add this to the Detail shelf on the scatter viz.

On the dashboard, add a Highlight dashboard action

Set the source and target sheets to be the scatter viz and change the Target Highlighting section to be Selected Fields, choosing the Dummy field.

And now, on selection, the other customer circles won’t fade away! Hurrah!

My published viz can be viewed here.

Happy vizzin!

Donna