Tag: Week 40

Can you create this ‘zoom and reset’ scatter plot?

Donna ColesLeave a comment

For this week’s #WOW challenge we’re focusing on parameter driven axes ranges.

Building the basic viz

After connecting to the dataset, add Sales to Columns and Profit to Rows. Add Customer Name to Detail and change the mark type to circle.

The chart is divided by reference lines which I chose to define as parameters (but these were additional to the ones mentioned in the challenge requirements).

pProfitRef

integer parameter defaulted to 0

pSalesRef

integer parameter defaulted to 5000

Add both parameters to the Detail shelf, the add a reference line on the Sales axis that refers to the pSalesRef value.

and then repeat and add a reference line to the Profit axis referencing the pProfitRef field.

To colour the 4 segments of the chart, create new fields

Sales per Customer

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

and

Profit per Customer

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

These capture the total sales / profit at the customer level, and we can then determine which quadrant each customer is in by

Cohort

IF [Sales per Customer]>=[pSalesRef] THEN
IF [Profit Per Customer]>=[pProfitRef] THEN ‘High Sales, High Profit’
ELSE ‘High Sales, Low Profit’
END
ELSE
IF [Profit Per Customer]>=[pProfitRef] THEN ‘Low Sales, High Profit’
ELSE ‘Low Sales, Low Profit’
END
END

Add this to the Colour shelf and adjust colours to suit. Then set the opacity to 50% and increase the size of the marks a bit.

To add matching coloured borders around the marks, add another instance of Profit to Rows. Change the mark type of the 2nd Profit marks card to Shape and change the shape to be an open circle. Set the opacity to 100%. Set the chart to dual axis and synchronise the axis.

Adjust the Tooltip and hide the right hand axis (uncheck show header).

Dynamically adjust the axis

The axes will change by adjusting them to refer to parameters. By default we need the axis to try to replicate what it gets set to automatically. For this we need to capture the maximum and minimum sales and profit values and add a ‘buffer’ to give the extra space. I played around with a few options for the buffer, so created a parameter to store this until I got the value that seemed to work best (again this was an additional parameter that I added to just help not having to change multiple calculated fields as I got the value I wanted.)

pBuffer

integer parameter defaulted to 2000

Then create 4 fields to define the max & min of the two measures +/- buffer

Min Sales + Buffer

{MIN([Sales per Customer]) – [pBuffer]}

Max Sales + Buffer

{MAX([Sales per Customer]) + [pBuffer]}

Min Profit + Buffer

{MIN([Profit Per Customer]) – [pBuffer]}

Max Profit + Buffer

{MAX([Profit Per Customer]) – [pBuffer]}

Then create 4 parameters which will define the values we ca use to set the axis

pX-Min

float parameter that is set to the Min Sales + Buffer field when workbook opens (selecting this will then populate the value)

Create further parameters

pX-Max

float parameter that is set to the Max Sales + Buffer field when workbook opens

pY-Min

float parameter that is set to the Min Profit + Buffer field when workbook opens

pY-Max

float parameter that is set to the Max Profit + Buffer field when workbook opens

Once all the parameters exist, edit the Sales Axis and change the axis to use a custom range that references the pX-Min and pX-Max parameters

Do the same for the Profit axis, but reference the pY-Min and pY-Max parameters instead.

Finally while we’re still on the workbook, create two new fields

True

TRUE

and

False

FALSE

and add these to the Detail shelf. We’ll need these later to stop marks highlighting when we click.

Name the sheet Scatter or similar.

Building the Reset button

This actually requires another sheet (even through the requirements says 1 sheet).

Create a new field

Reset Axis

‘Reset Axis’

Add this to the Text shelf of a new sheet. Change the mark type to shape and select a transparent shape (refer to this blog to understand how to create this).

Set the view to Entire View and align the font middle centre and increase the font size. Set the background of the whole worksheet to black. Adjust the tooltip. Add Min Sales + Buffer, Max Sales + Buffer, Min Profit + Buffer and Max Profit + Buffer to the Detail shelf, along with the True and False fields.

Name the sheet Reset or similar

Adding the interactivity

Add the Scatter and Reset sheets to a dashboard, removing any parameters/legends etc that get added. Create dashboard parameter actions to set the axis parameters when selections are made on the scatter plot:

Set X Max

On select of the Scatter sheet, set the pX-Max parameter, passing in the maximum value of the Sales field.

Set X Min

On select of the Scatter sheet, set the pX-Min parameter, passing in the minimum value of the Sales field.

Set Y Min

On select of the Scatter sheet, set the pY-Min parameter, passing in the minimum value of the Profit field.

Set Y Max

On select of the Scatter sheet, set the pY-Max parameter, passing in the maximum value of the Profit field.

Also create dashboard parameter actions to ‘reset’ the axis parameters when the Reset button is clicked:

Reset X Min

On select of the Reset sheet, set the pX-Min parameter, passing in the minimum value of the Min Sales + Buffer field.

Reset X Max

On select of the Reset sheet, set the pX-Max parameter, passing in the maximum value of the Max Sales + Buffer field.

Reset Y Min

On select of the Reset sheet, set the pY-Min parameter, passing in the minimum value of the Min Profit + Buffer field.

Reset Y Max

On select of the Reset sheet, set the pY-Max parameter, passing in the maximum value of the Max Profit + Buffer field.

All these 8 actions should now combine to drive the ‘zoom & reset’ functionality.

Finally, the last step to make the display ‘nicer’ is to deselect the marks from being highlighted when selected. Add a dashboard filter action

Deselect Scatter

on select of the scatter object on the dashboard, target the scatter sheet directly, passing the selected fields of True = False. Show all values when the selection is cleared.

Repeat an create a similar action for Deselect Reset.

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

Happy vizzin’!

Donna

Can you create a brush filter?

Donna ColesLeave a comment

This week’s #WOW2024 challenge was set by Yusuke, challenging us to create a filter for the line chart, using selections from the bar chart. The main aim was to make it as easy to select months with low sales as it is to select months with higher sales.

Building the bar chart

Create a new field

Monthly Sales

[Sales]

and format to $ Thousands (K) with 0 dp.

Also create

Order Date Month

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

Add Order Date Month to Columns at the continuous month level (green pill) and add Monthly Sales to Rows. Change the mark type to Bar and set the size of the bar to as wide as possible. Edit the date axis, and remove the title, then fix the tick marks to start on 1st Jan 2021 with an interval of every 1 year.

Create a set call Order Date Month Set, based off of the Order Date Month field (right click the field > create > set, and select a set of dates). Add Order Date Month Set to the Colour shelf and adjust the colours accordingly. Add a dark grey border to the bars too. Modify the Tooltip to suit.

Create a new field

Max Monthly Sales

{MAX({FIXED [Order Date Month]: SUM([Sales])})}

(for each month, get the sum of sales, then return the maximum of all these).

Add this field to Rows. On the Max Monthly Sales marks card, reduce the opacity to 25% and remove the border (all on the Colour shelf)

Set the char to Dual Axis and synchronise the axis. Remove the Measure Names field from the All marks card.

Hide the right hand axis, remove all row and column dividers. Darken the row gridlines slightly, and add the instructional text as the title of the sheet.

We are ultimately going to make use of set actions to define the dates selected by the user. For this we will need to pass the exact date selected, so add Order Date Month to the Detail shelf of the All marks card as a continuous exact date (green pill).

We’re also going to not want the bars to be ‘highlighted’ when selected, so create fields

True

TRUE

and

False

FALSE

and add both to the Detail shelf of the All marks card.

Building the Line Chart

Create a new field

Selected Sales

IF [Order Date Month Set] THEN [Sales] END

and format to $ Thousands to 2dp.

On a new sheet add Order Date to Columns at the continuous day level (green pill), add Region to Rows and add Selected Sales to Rows.

Change the colour of the line and set line markers (via the colour shelf) and reduce the size of the line. Show mark labels, and set to just label the maximum value per pane.

Adjust the row banding so the band size is set to 1

Remove the column dividers, but set the row dividers to be darker grey. Adjust the row gridlines to be a slightly darker grey. Adjust the title of the Selected Sales axis, and remove the title from the date axis. Format the data axis, so it displays a custom date format of mmm dd. Right click the Region label at the top of the chart and hide field labels for rows.

Create fields

Min Date

{MIN(IF [Order Date Month Set] THEN [Order Date Month] END)}

which will return the date of the earliest month selected in the set and

Max Date

DATE(DATEADD(‘day’, -1,
DATEADD(‘month’, 1, {MAX(IF [Order Date Month Set] THEN [Order Date Month] END)})
))

which finds the maximum month selected in the set (which will be 1st of the max month), adds on a month, and takes off a day to get the last day of the maximum month.

Add these to the Detail shelf as continuous exact dates, and then update the Title of the sheet to reference the fields.

Then create

Tooltip: Date

[Order Date]

and add to the Tooltip and adjust the Tooltip to suit.

Finally, depending how the user selects the dates, there may end up being a break in dates. Right click on the Order Date Month Set and select Show Set. Adjust the dates, so there is at least 1 unselected value between the dates.

To make a continuous line between the dates, click the context menu against the Selected Sales pill on Rows and select Format. On the options on the left hand side, select Pane and at the Special Values option, select Marks: Hide (Connect Lines).

Adding the interactivity

Put the 2 sheets onto a dashboard. Create a dashboard set action

Select Months

On select of the bar chart, target the Order Date Month Set by assigning values to the set when the action is run, and keeping set values when the selection is cleared.

To stop the bars from highlighting on selected, create a dashboard filter action

Deselect Marks

On select of the bar chart on the dashboard, target the Bar Chart sheet, passing in the fields True = False.

And this should now complete the challenge. My published viz is here.

Happy vizzin’!

Donna

Let’s Analyse New Product Sales

Donna ColesLeave a comment

For the challenge this week, Sean wanted us to identify the sales of ‘new’ products compared to previous years. This challenge was born out of a requirement he had at work, where he discovered the definition of a ‘new product’ was different to what he’d assumed.

In this instance, the ‘client’ only cared about comparing sales in the same month across the years. Any sales related to the products outside of the specific month were irrelevant and were to be ignored. A product was then counted as ‘new’ in the first year (of the specified month) that there was a sale.

In this case we are always looking at the month of September (the previous month to ‘today), so if Product X was sold in Sept 2021 and Sept 2023 only, then Product X would count as new in Sept 2021, and only the sales for Product X in Sept 2021 would be included in the subsequent values displayed.

Now I thought I’d nailed my understanding of the requirement, and went ahead building out a tabular view of the data using table calculations, but my final output did not match Sean’s result – in fact it was quite way off. I stepped through my logic, even sharing some samples with Sean to sense check, and we seemed to concur on the products identified as new, so I was incredibly puzzled as to what was causing the value discrepancy.

I messaged my friend, Rosario Gauna, to share my numbers and see what she came up with…. she matched Sean! Doh! I then had to spend time dissecting my solution and Rosario’s to see which products I was/wasn’t counting. Eventually I found the problem….

In using table calculations, I had included all the fields the viz required which included the Segment (Home Office, Corporate, Consumer). This meant that in my logic to identify a ‘new product’, I was counting the product as new in the first year that the product was sold in each Segment – ie if Product X sold in Sept 2022 against Segment Home Office and then sold in Sept 2023 against Segment Corporate, I counted both sales as being ‘new’ as the segment differed. However I only wanted the Sept 2022 sale to count as ‘new’.

With this understood, I tried to revise my initial table calc build, but to no avail. So I opened up a new workbook and started again, this time taking a different track. This is what I’ll blog, but I felt it important to explain how even a requirement I thought I’d understood, could still get mis-interpreted.

Building out the calculations

The first requirement is that we’re only looking at comparing the data for the previous month to ‘today’. If this was being developed for a live business application we’d reference the TODAY() function, but since I want this viz to continue to display on Tableau Public after we hit 2024, then I’m going to use a parameter to hardcode ‘today’.

pToday

date parameter defaulted to 4th October 2023

From this, I can then determine the month we need to compare

Month to Compare

DATEPART(‘month’, DATEADD(‘month’, -1,DATETRUNC(‘month’, [pToday])))

working from right to left (inside to out), this takes ‘today’ and finds the 1st of the month (ie 1st October), then goes back 1 month (ie 1st September), then gets the month number ie 9.

As this returns a number, it automatically is listed in the ‘measures’ section of the data pane (under the line), but I dragged it into the top half as I want the number to be discrete.

I then created

Filter Month

DATEPART(‘month’, [Order Date]) = [Month to Compare]

which returns true for all the orders where the month of the Order Date is in September.

Let’s build up a table so we can start to see what we’re working with

Add Product Name and Filter Month to Rows, Order Date (at the Year level) to Columns and Sales to Text.

In the example above, 3-ring staple pack has sales in September 2022 and in other months in 2020 and 2021, but we don’t care about those months, so this product is counted as new in 2022, with a value of $11.

3M Hangers With… have no sales in September in any year, so we won’t be counting that product at all.

6″ Cubicle Wall Clock has sales in both September 2022 and 2023, so this product will be counted as new in 2022 with a value of $83.

So we can filter the date just to Filter Month = True (move Filter Month from Rows to the Filter shelf).

Now we want to identify the earliest year for each Product Name.

Year First Purchased in Month

{FIXED [Product Name],[Filter Month]: MIN(YEAR([Order Date]))}

Add this onto Rows, and you can see below we’re picking up the right year.

Now we can identify the value of the sales we want to count.

New Product Sales

IF [Year First Purchased in Month] = YEAR([Order Date]) THEN [Sales] END

Only get Sales for the year that matches the minimum year. Format this field to $ with 0 dp.

Adding this in to the table, we can see we only have a value against the first year.

So we’ve identified the sales values of the products we care about. We can now aggregate this at a higher level, and incorporate the other dimensions.

Remove Sales from Text, add Category to Rows and Segment to Columns. Remove Product Name and Year First Purchased in Month.

The viz displays the % of total new product sales by Category per Segment. Right click on the New Product Sales pill and add Quick Table Calculation, selecting Percent of Total. Right click on the pill again and select Compute Using -> Category. Right click on the pill and Format and format to % with 0dp. Add another instance of New Product Sales back into the table.

Now we want to be able to compare the sales for the current year/category/segment against that of the previous year.

Previous Year Sales

LOOKUP(SUM([New Product Sales]),-1)

This looks up the New Product Sales value of the previous (-1) field. Add this field into the table, and right click and Edit Table Calculation. Adjust so the calculation is computing by Year of Order Date only.

You should be able to see that the values from the previous year for each Segment & Category are now displayed against each cell. As there is no data for 2019, there are no values listed for this field against the 2020 fields.

Now we have the values we want to compare in the same ‘row’ of data, we can compute

% Diff From Previous Year

(SUM([New Product Sales]) – [Previous Year Sales]) / [Previous Year Sales]

custom format this to â–²0%;â–¼0% (use this site to get the images to copy & paste), and add this into the table ensuring the table calculation is set to compute using Year of Order Date only, as you did above.

And now we have all the components needed to build the viz.

Building the bar chart

Duplicate the table sheet, then apply the following steps

  • Move % of Total New Product Sales to Rows
  • Move Category to Colour and adjust accordingly
  • Move New Product Sales and % Diff from Previous Year to Label
  • Remove Previous Year Sales
  • Add another instance of % of Total New Product Sales to Label ( I tend to do this by pressing ctrl, and then clicking on the pill in the Rows and dragging onto Text – this creates a duplicate of the pill and preserves the table calculation settings. Otherwise add an instance of New Product Sales to Label, then add the Percent of Total Quick Table Calculation and ensure the pill is set to compute by Category.)
  • Move Segment to be in front of the Year(Order Date) field on Columns – if this changes the viz display, ensure the mark type is set to bar.

Tidy up the display by

  • removing all gridlines/zero lines etc
  • remove row dividers
  • Adjust title of the y-axis
  • Hide the Segment/Order Date column heading
  • Hide the tooltips

The text and formatting of the labels also needs to be adjusted. We don’t want the ‘vs PY’ text displaying for the 2020 years when there is no difference from previous year.

Label PY Text

IF NOT ISNULL([Previous Year Sales]) THEN ‘vs PY’ END

Add this to the Label shelf and ensure the table calculation is set to compute using YEAR of Order Date only. Then adjust the layout of the label text and the format the size of the text.

Now you can just add to a dashboard!

Happy vizzin’!

Donna

Applying accessibility principals to data visualisation

Donna ColesLeave a comment

It’s Community month for #WOW2022, and so guest poster Collin Smith set this challenge to get us all thinking about how to make an accessible dashboard. There were 3 tiers of requirements, so lets start with Tier 1.

Building the charts

I used 5 sheets to create the core of this viz. One for the KPI title, 1 for each KPI and 1 for the bar chart. Typically I would have tried to build the KPI section all on a single sheet, but when I interacted with the solution to get a feel for how the tabbing worked (and given the hint in the requirements, not to use more than 5 sheets), I figured it would be necessary.

First up we need to define a parameter to use for the data selector. For this I created

Order Date Week

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

and then adjusted the default sort order of the new field to be descending (right click pill -> default properties -> sort)

I then created the parameter to reference this

pSelectedWeek

date parameter where the values were added from the Order Date Week field. Default value set to 09 Aug 2021 (the data set didn’t match whatever Collin had used as my data went from 2018-2021, so I used a week in 2021 which was about equivalent to the week Collin had used, 10 Aug 2020).

The default sort on the field applied above, meant the dates got added in descending order.

I then created a field to filter the KPI charts

Is Selected Week?

[Order Date Week]=[pSelectedWeek]

this returns true or false.

On a new sheet, add Is Selected Week? to Filter and set to true. Then add Region to Filter and select East, West and South. Set the mark type to Polygon. Click the Tooltip button and clear the Show Tooltips option. Update the Title of the sheet as below, referencing pSelectedWeek and Region. Use Verdana 16 pt.

On a new sheet, again add Is Selected Week? to Filter and set to true. Then add Region to Filter and select East, West and South. Set the mark type to Polygon. Click the Tooltip button and clear the Show Tooltips option. Adjust the properties of the Sales field so it is formatted to $ with 0dp, then add Sales to the Detail shelf. Edit the title of the sheet to be where the word Sales is Verdana 16pt and the referenced Sales field is Verdana 32 pt. Note – this isn’t how I originally built this KPI, but I had to modify my approach to allow the screen reader to be able to read the values out (requirement Tier 3).

Repeat this process on a new sheet for the Profit KPI. And the similarly for the Profit Ratio KPI (note you’ll need to create the field first

Profit Ratio

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

and format to % to 1 dp.

To build the bar chart, we need a parameter to drive the metric selection.

pSelectMetric

I used an integer data type and assigned 1 to Sales and 2 to Profit, defaulted to 2.

From this I could create

Metris to Show

CASE [pSelectMetric]
WHEN 1 THEN [Sales]
WHEN 2 THEN [Profit]
END

On a new sheet I then again added Is Selected Week? to Filter (true) and Region to Filter (East, South, West). Then I added Product Name to Rows and Metric To Show to Columns and sorted by Metric To Show descending.

I then add Product Name to Filter and filtered by the Top 10 Metric To Show

Initially this may make everything disappear, but don’t panic. Click on the Is Selected Week? and Region fields in the Filter shelf and Add to Context.

Adding to context means the filters are applied before the top 10 is computed. Before, the top 10 filter was being applied first and then the other filters.

All of our sheets have 2 filters in common and we need to ensure they’re all working together. So now click on the Is Selected Week? filter and Apply to Worksheets > Selected Worksheets and select the 4 KPIS sheets you’ve built. Repat this with the Region filter. Only the bar chart should have the additional Product Name filter.

Now we need to format the look of the bar chart.

  • Add Profit Ratio to Colour
  • Add Metric To Show and Profit Ratio to Label. Format the label as below to Verdana 10pt.

  • Make each row a bit wider
  • Format the Product Name header for each row to be Verdana 12 pt.
  • Make the Product Name column a little wider
  • Add a dotted Row Divider to the Header level only

  • Add a thick black line to the Axis Ruler of the Rows

  • Hide the Metric To Show axis
  • Hide the Product Name column label (right click > hide field labels for rows)
  • Set Axis Ruler for Columns to None
  • Update the Tooltip using a mix of Verdana 16pt and 12pt

It should be looking something like…

Next, we need to give a title above the bar chart, which needs to be dynamic based on the metric selected. Create

Axis Label

CASE [pSelectMetric]
WHEN 1 THEN ‘Sales | Profit ratio’
WHEN 2 THEN ‘Profit | Profit ratio’
END

Add this to Columns.

Now to add the title. For this we need the name of the top Product Name. We need a new field for this

Top Product

WINDOW_MAX(IF SUM([Metric To Show]) = WINDOW_MAX(SUM([Metric To Show])) THEN MIN([Product Name]) END)

Let’s dissect this… WINDOW_MAX(SUM([Metric To Show])) returns the largest (maximum) value of Metric To Show that is being displayed. So we then test to find the row that has this value, and when a match is found we return the Product Name. This means we’ll get the Product Name for the first row of data, and NULL for the rest. The outer WINDOW_MAX is then finding the max value of the Product Name or Null, which again will be Product Name, and this value is then ‘spread’ across all rows. This means we have a single value for the top Product Name stored against every row, so when we reference it in the title we only have one value.

Add Top Product to Detail, then update the title as below using a mix of Verdana 16pt and 12 pt.

Finally, add the caption (Worksheet -> Add Caption) and adjust the text to match the required wording, referencing fields/parameters where appropriate.

Building the Dashboard and setting Focus (Tier 2)

Using a mixture of horizontal and vertical containers, I added all the necessary objects to the dashboard. I added the title and subtitle in a text box using a mix of Verdana 32pt and 16pt. I updated the titles and of format of the parameters and filters to use Verdana 12pt, and I added my standard footer. I then read through the community post Collin had provided to get an understanding on how to set the focus. As a result, I renamed all my containers and objects in the Item hierarchy.

Based on Collin’s viz, I needed to define the order of focus as

  • Filter-Week
  • Filter-Region
  • Filter-Metric
  • View-KPI Title
  • View-SalesKPI
  • View-ProfitKPI
  • View-ProfitRatioKPI
  • View-Bar

As per the instructions, this was going to require an edit to the XML of the workbook. So I did as advised and saved a copy of my workbook so I had a backup in case it all went wrong.

I then made a note on a piece of paper of each of the above fields and assigned a unique number against it ie Filter-Week 10, Filter-Region 20 etc.

I then opened up Notepad++ and opened the twb file in the editor.

I searched for dashboard and found the tag mentioned in the article and changed the relevant property to false

I then searched for zone, and went through changing the ID of every <zone> section. Anything that wasn’t part of my list above, I set to a unique sequential number from 100. When I hit one of the zones above, I set the value to the number I’d written down on paper. Once done I saved the file, closed notepad and reopened the workbook in Desktop. It loaded – phew! I then published to Tableau Public to test the tabbing functionality and it matched.

Testing the Screen Reader (Tier 3 requirement)

As I use a Windows machine, I used the inbuilt Narrator app. I used it against Collin’s solution to understand what I should expect. and then tested it against mine. Initially mine wouldn’t relay the ‘To open view data pane, click control, shift, enter’ instruction that was explicitly stated. I did some reading up (here) and verified the bar chart allowed the View Data control to show on the tooltip, I loaded the View data window in Desktop, and after publishing made sure the workbook was downloadable. However, I still couldn’t get this information to read out <shrug>. So I messaged Collin – what was I missing? The reply… add the required text in small white font, so it doesn’t display, but can be read! Bah! So simples in the end!

My published viz is here.

Happy vizzin’!

Donna

Can you make stacked bar charts easier to compare?

Donna ColesLeave a comment

Candra McRae was back to set the challenge this week. I found it relatively straightforward, so if you’re relatively new to Tableau, this is quite a good challenge to start with. In this we’ll cover

  • Grouping the states
  • Applying the sort
  • Adding the total value
  • Adding the interactivity

Grouping the states

The states need to be grouped based on the initial letter. Candra stated she wasn’t expecting a large IF STARTWITH… type formula. I did it by making use of the fact characters can be converted into ASCII which provides a numerical representation of a letter, which we can then utilise. So we need

State Initial ASCII

ASCII(UPPER(LEFT([State],1)))

This takes the 1st letter of the State, ensures it is uppercase, and converts to ASCII. You can see below what this looks does

With this knowledge, we can then create

State Group

IF [State Initial ASCII] <=77 THEN ‘A-M’
ELSE ‘N-Z’ END

and you can now easily create the stacked bar chart (note, I’ve already removed the various gridlines etc)

Applying the Sort

The ultimate intention is to capture the Category a user clicks on into a parameter, so we need to define that parameter

pSelectedCategory

A string parameter defaulted to Office Supplies

Show this parameter on your sheet, so you can manually test how the sort will work by manually changing the values.

Create a new calculated field to define the sort

Sort

IF [pSelectedCategory]=[Category] THEN 0 ELSE 1 END

Then edit the sort property of the Category field that’s on the Colour shelf as below

Now change the value in the parameter box to Technology and see how the chart changes.

Adding the total value

Create a new field to store the total values

Total Sales by State Group

{FIXED [State Group]: SUM([Sales])}

Add this onto the Detail shelf, then add a reference line per line as below

Adding the interactivity

Once you’ve added the chart onto a dashboard, you need to add a parameter action which will set the pSelectedCategory parameter with the value from the Category field on select.

To prevent the selected category from ‘remaining selected’ on click, I applied the ‘true=false’ trick I use a lot.

Create a field True = true and a field False = false, then add both to the Detail shelf of the chart viz. On the dashboard add a new filter action which on select passes selected fields only setting true = false. As this condition can never be true, the filter doesn’t apply and this clears the highlight action.

And that is it for this week – short & sweet, but covers a handful of bite-size concepts. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Table Enhancements for #SportsVizSunday and Pro Football Focus

Donna Coles1 Comment

Community month continues for #WOW2020, with this week’s challenge being set by Spencer Baucke.

This week the challenge focuses on 3 techniques for enhancing a tabular display

  • Drill Down / Expand to show the breakdown by Year
  • Custom Header to control sorting
  • Page Control

I managed this challenge, but it wasn’t without help – I did my research using my friend, Google, and found tutorials to help, so this blog is briefer as you’ll need to read the blogs I reference too 🙂

Drill Down / Expand

There was a similar challenge last year which I blogged about here… or rather I referenced Rosario Guana’s excellent blog here.

The slight difference between this and the current challenge is that on expand/drill down, the Season is being shown in a separate column, rather than within the same one as above. The need for ‘data duplication’ which is referenced in Rosario’s blog isn’t required in this case. The key fields I used to resolve this part of the challenge are

pSelected Player ID

An integer parameter, defaulted to 0

pLevel

An integer parameter, defaulted to 2

Max Level

IIF([pLevel]=1,2,1)

If pLevel is 1 then we’ve already ‘drilled down’, so the max level on display is 2, otherwise we’ve yet to drill down, so the max level on display is 1.

DD Level

IIF([Max Level]=2 AND [Player Id]=[pSelected Player ID],2,1)

If we’ve already drilled down somewhere and you’re on the player we drilled down to, then the drill down level is 2, otherwise it’s 1.

Player ID Arrow

IF [pSelected Player ID] = [Player Id] AND [Max Level]= 2 THEN ‘â–¼’
ELSE ‘â–º’
END

If we’ve already drilled down somewhere and you’re on the player we drilled down to, then we want to display the down arrow. I use this site to get the geometric shapes required.

Season Display

IF [pSelected Player ID] = [Player Id] AND [Max Level]=2 THEN STR([Season])
ELSE ”
END

If we’ve already drilled down somewhere and you’re on the player we drilled down to, then we want to display the Season otherwise it needs to be blank.

Player ID Display

Player ID

This is just a duplicate field.

The fields are then arranged along with the measures as below

Player ID is hidden (it will used for the sort later). DD Level is added to the Detail shelf, so it can be referenced by the parameter actions.

NOTE – I had to ask as it wasn’t obvious to me, but the Carries measure is essentially the number of records or count of the table, so Avg YPC becomes SUM([Yards])/[Carries].

To complete the drill down, when added to the dashboard, the following parameter actions are required

Selected Player

passes the value from the Player ID field into the pSelected Player ID parameter

Set Level

Passes the DD Level field into the pLevel parameter

Custom Header to Control Sorting

I followed Tessellation’s blog post, Ultimate Guide to Tables : Headers pretty much verbatim to build the header sheet. Here’s just a few pointers below on how my solution matches with the blog.

For the Measure Rows field referenced, I used the Position field as a field in the data set that contained at least 3 values :

IF [Position] = ‘FB’ THEN -1
ELSEIF [Position] = ‘H’ THEN 0
ELSEIF [Position] = ‘P’ THEN 1
END

I created parameters

pHeader MeasureNames (which is the equivalent of MN Parameter referenced in the blog).

and pHeader Position which is the equivalent of the Category Parameter mentioned

The parameter actions were then set as

Header – Set Sort Measure

passes Measure Names to the pHeader MeasureNames parameter

and

Header – Set Sort Direction

passes the Position field into the pHeader Position parameter

When defining the other variables referenced in the blog, I had an issue with the Measure1 TF field; my version looks like

IF [pHeader MeasureNames ] = ‘Measure1’
AND [pHeader Position] = [Position]
THEN TRUE
ELSE FALSE
END

For the sorting, I created

Sort Measure Value

CASE [pHeader MeasureNames ]
WHEN ‘Measure1’ THEN [Carries]
WHEN ‘Measure2’ THEN SUM([Yards])
WHEN ‘Measure3’ THEN [Avg YPC]
WHEN ‘Measure4’ THEN SUM([TDs])
END

and then Sort

IF [pHeader Position] = ‘P’
//up arrow selected, sort ascending
THEN -1 * [Sort Measure Value]
ELSE [Sort Measure Value]
END

Note – I chose my up and down arrows to behave different from how they are described in the blog. To me clicking ‘up’ suggests a sort ‘upwards’ ie ascending from small to large.

The Sort field is then used to apply the sort to the Player ID field on the table

Page Control

Again I followed a blog by Tessellation – Ultimate Guide to Tables in Tableau : Pagination

The exception, was at Step 5 Build the page navigator, I simply created a new sheet called ‘Curr Page’. This referenced 2 calculated fields

Current Page No

[pPage No]

where pPage No is the equivalent of the page number parameter referenced in the blog.

Total Pages

FLOOR({COUNTD([Player Id])}/[pRows Per Page])+1

where pRows Per Page is the equivalent of the rows to show parameter referenced in the blog.

The only other slight addition required to this challenge, was to reset the page control to page 1 if the sorting was changed. This was done by creating a new calculated field

First Page No

1

adding this field to the Detail shelf on the header/sort controls sheet, then adding a parameter action

Reset to Page 1

which passes the First Page No to the pPage No parameter

Fingers crossed, I think I’ve covered the main points for this challenge and highlighted where the blogs might differ. My published viz is here.

This has given some great ideas of how to ‘pimp’ a table. I highly recommend you check out some of Tessellation’s other blog posts on tables :

Happy Vizzin’! Stay Safe!

Donna