Tag: Highlight Actions

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