Tag: Sorting

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

Can you sort dimensions with a single click?

Donna Coles3 Comments

Luke kicked off the 1st workout of 2020 with this challenge – using a ‘header’ sheet to control the sorting on a tabular view below.

Building the table

A couple of measures need creating initially :

Sales / Order

SUM([Sales])/COUNTD([Order ID])

Profit Ratio

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

The table consists of 3 measures aligned side by side, of different mark types : bar, text, bar. Sub-Category exists on the Rows.

The Sales measure is displayed first on the Columns, with mark type set to Bar, and the axis is fixed to start at 0 so there is minimal spacing between the Row label and the start of the bar. An additional calculated field [Profit Ratio]< 0 is added to the colour shelf and adjusted to be red/grey as appropriate.

As the requirement for the text Sales/Order measure was to right align it, adding Sales/Order measure to columns won’t work. Instead use MIN(0) setting the mark type to Gantt Bar, and making the mark type as small as possible, and setting the opacity to 0. Sales/Order is then added as a label which is then aligned middle left, and therefore right-aligning the text on the screen.

The final measure, Profit Ratio is added to the Columns shelf, also as mark type of Bar. Once again the [Profit Ratio]< 0 is added to the colour shelf.

All row/column lines, gridlines, zero lines and axis lines were set to nothing, but the Profit Ratio measure required a zero line to display. Using the zero line setting of the formatting would have meant a zero line would also display on the Sales bar chart, which wasn’t required. Instead I used a constant reference line of 0 on the Profit Ratio axis only.

And finally, the requirement stated that sort controls on the table should be switched off. This setting can be found under the Worksheet menu. All the axis were then hidden.

Building the ‘Header’ sheet (basic)

To start off I’m just going to describe how to construct a basic header table so it can then it interact with the table on the dashboard, and apply the sorting. I’ll revisit this all later, as getting the header to meet the specific requirements Luke stated was something I admit I struggled with.

At it’s simplest, you can build the header as follows:

  • Measure Names on rows and filtered to just the 3 measures we need
  • Measure Names on Text and aligned Middle Centre
  • Measure Values on Detail
  • Measure Names manually sorted in the order we want
  • Show Header unchecked for Measure Names on the rows shelf

Invoking the Sort

The sort is intended to work by clicking on a label on the header sheet, which in turn should sort the data in the table sheet.

To start off, a string parameter is required which is defaulted to the value Sales

A calculated field is also needed to define the sort measure, based on the sort parameter

Sort By

IF [Sort] = ‘Sales’ THEN SUM([Sales])
ELSEIF [Sort] = ‘Profit Ratio’ THEN [Profit Ratio]
ELSEIF [Sort] = ‘Sales / Order’ THEN [Sales / Order]
END

On the Table sheet, the Sub-Category field is then set to sort based on the Sort By field

With the Header sheet and the Table sheet added to a dashboard, a Parameter Action can be created which will set the Sort parameter based on the measure name clicked on :

At this point now, you should have the basics of the challenge – a header sheet causing the data in the table sheet to sort based on the label clicked on.

However, Luke added additional complexity to the Header sheet :

  • add an â–¼ to identify the column label clicked on
  • change the font to be darker for the label clicked on
  • ensure the column labels not clicked on, remained ‘visible’ (ie did not appear to fade as per standard behaviour when data is selected in a viz).

Building the Header (Initial attempt)

My initial attempt to meet the stated requirements made use of a dual axis MIN(0) table, where one axis was of mark type square, with the Measure Names & ‘ â–¼ ‘ on the label shelf, which was formatted to be a dark font, and set to only show when highlighted. The second axis was a Text mark type, with Measure Names on the text, formatted to a lighter font.

On click on one of the labels, the label associated with the square mark type would be shown, but the other labels ‘faded out’.

Whatever I tried to do to stop them, based on tricks I’ve tried in the past (using a Dummy field and applying a highlight action on the dashboard), didn’t work.

As much as I tried I couldn’t get beyond this, so published this as my solution, and waited for Luke to release his workbook. My version using this method is here.

Building the Header (Luke’s method)

Once Luke made his workbook available, I downloaded it to see what he’d done to keep all the header labels ‘enabled’ on click.

He used a dashboard action filter to set a calculated field ‘True’ to match a calculated field ‘False’. Both these calculated fields had to exist on the Detail shelf of the header sheet. (also note how the dashboard source sheet selects the Header v2 view which targets the Header v2 sheet directly (and not the equivalent view on the dashboard).

This is a technique Luke has blogged about on his own site following a discussion in the Tableau Community : https://www.tessellationtech.io/automatically-deselect-marks/

I have to admit, I had seen this post, and am kicking myself I didn’t remember it to try it out 😦

So…. back to my viz, and I applied Luke’s deselect marks idea to my header sheet, but it didn’t work. As I’d made the arrow show on ‘highlight’, this filter action was undoing the highlight, so preventing my label to show.

So I figured I’d just have to copy what Luke had done… and I started to build out his sheet… 3 columns of Min(0) set to Text with Measure Names on the Text :

All I could display was Min(0)… the only way I could see to get the words Sales, Sales/Orders, Profit Ratio displayed, was to add in Measure Values to the Detail shelf… but then all the words overlapped…

So I messaged Luke, as I just couldn’t understand this black magic… but before I got a response, I had a lightbulb moment….

Aliases!

The MIN(0) in the Measure Names had been aliased, to look like the same name of other measures, but I added a leading and trailing space around them all, as you can’t have duplicated names.

This really is something sneaky! but I was quite chuffed I found it before Luke told me.

To get the display and the formatting right, then required an ‘Arrow’ field for each measure along the lines of :

Arrow for Sales

IF [Sort] = ” Sales “
THEN “â–¼”
ELSE ” “
END

(note the leading & trailing spaces). This field is added to the Text shelf on the 1st Min(0) marks card. An equivalent field is then required for the other 2 measures, which are added to their respective marks cards.

Sort – Sales

[Sort] = ” Sales “

This boolean field is then added to the Colour shelf of the 1st Min(0) marks card. Again an equivalent is required for the other 2 measures. Setting the colours correctly will require each label to be clicked on the dashboard to get all the true/false permutations to show.

My revised version to meet the requirement is available here. Be aware if you do download, the field naming might not be identical to that above. This is because I have 1 workbook with both solutions in it, so in some cases needed to have duplicated versions of the calculated fields/parameters, to ensure both versions continued to function.

That was certainly an interesting challenge for Week 1 of the new year, and ultimately I was stumped! Hoping this isn’t going to be a theme for the weeks to come 🙂

Happy vizzin’!

Donna