Sorting by a field in a secondary data set

One of my biggest frustrations with Tableau is the limited facility that comes when linking to a secondary data set. One of these is the inability to sort fields from the secondary data source.

Maybe I have my invoiced amounts in my primary data source by relationship reference, and a secondary data source that tells me the salesperson for each relationship reference.

And I want to report the total amount invoiced by salesperson.

I can do that quite simply. I drag the invoice amount into the body of the view from the primary data source. I then flip to the secondary data source, click the chain link to make the data sets linked, and drag the salesperson into my rows.

Now if salesperson had been in the primary data source, the sort buttons in the toolbar would be clickable. Or if you clicked the dropdown arrow on the Salesperson pill in the rows, a Sort option would have been available. We could have sorted the view by amount, showing the salesperson with the highest total invoice amount at the top.

But because the field has been brought from the secondary data set, we’re presented with this.

Grey sort

And this.

Salesperson

And so we can’t sort.

Although perhaps we can, with a little hack.

If you want to sort in descending order, then create a calculated field in your primary data source that which is simply the negative of the field being reported

Equation

Now drag that field into the rows next to your Salesperson. As it is a measure, it’ll be treated as being continuous and will be green, and will only be allowed to the right of Salesperson. And because it’s coming from a different data source, it’ll appear as an aggregate field.

Now click the new pill’s dropdown and change it to discrete. It’ll turn blue, and you can now reorder the two row fields so that the aggregate variable appears first.

Rows

And now, because you have a field from the primary data source in your rows, you can click the dropdown on the AGG([Inv Amt (neg)]) field and you’ll see a Sort menu option.

Click this and you’re presented with only two options. You can either sort by the data source or you can manually configure the sort order based on the values you see. Most of the time you’ll want to sort by data source. (It only allows low to high sorting here, not high to low. Hence our need to create a negative version of our variable.)

So by sorting in ascending order by a negative field, it’s the equivalent of sorting a positive field in descending order.

Your first column headings will be the negative versions of the values in the table. Now you’ll probably want to hide this first column, so you can simply make the text white and narrow the column to its narrowest possible width.

And there you have it.

Note: if there’s any likelihood that two values in the table will be the same, you’ll need to make them subtly different. I often do this by adding a very small but variable to the calculated field that you just created. I often do this by finding a couple of numbers that are generally quite large and fluid in the data sets and dividing 1 by their product:

1/([Sales volume] * [Outstanding amount]

If you add this to the calculated field that you calculated earlier, then the two figures will be different by a fraction of a penny, and they’ll appear as separate ungrouped items in the table.

2 thoughts on “Sorting by a field in a secondary data set

  1. Neat post, thanks for sharing – it’s amazing how useful discrete pills are for sorting not just secondary sources but also sat Table Calculations.

    By the way one other idea to separate the fields would be to add a “random()” field.

    • dan@osirra.com says:

      Thanks, Chris.

      You’ve just taught me about the random() function. Oddly, it’s not listed in the list of functions on the right of the equation pop-up; and it doesn’t prompt you if you start to type the function name. (Other similar functions that carry no arguments (such as TODAY()) are prompted.

      Very strange.

Leave a Reply

Your email address will not be published. Required fields are marked *