13 Replies Latest reply on Dec 5, 2018 4:21 PM by Alex Neumann

# Sorting based on Quick Table Calculation (Percent of Total)

A very simple thing is driving me crazy:

I am trying to automatically sort the list of countries by their respective % value (automatic calculation computed using the field "IsUnknownVehicle" which is hidden from the view).

Clicking on the sort button produces the desired behaviour but I want to make the sort automatic.

Browsing through the forum post lead me to believe it involves nested sorting but I just can't get it to work.

Would really appreciate some help here. Thank you.

• ###### 1. Re: Sorting based on calculated field (percentage)

Can you not sort the Country by using the % calculated field?

Right click on the Country pill and click Sort. In the Sort options there is a choice to Sort by Field. Choose the % calculated field and that should sort how you want. Hopefully I'm not oversimplifying things...

Andrew

• ###### 2. Re: Sorting based on calculated field (percentage)

Thierry,

If you put the Country dimension to the left of the IsUnknownVehichles measure (?), then do what Andrew mentioned, the sort should work. If you leave the dimension to the right of the measure it won't sort at all, as you've discovered. I've attached a Superstore sample.

--Shawn

• ###### 3. Re: Sorting based on calculated field (percentage)

Thank you Shawn and Andrew for helping out. The problem with my view is that I cannot use the field "Number of Records" as my sorting field.

Please see below the full view. I am displaying hidden data and header and also added a "Number of Records" to illustrate my point.

What I want to do is to sort the "Countries" based on their respective percetange of "Known" values.

Since that percentage is calculated as a ratio between known and unknown within each country, I cannot use the field "Number of Records" in my sorting field.

I should also probably have mentioned more clearly that this "% measure" is not a "calculated field" per se but rather a "Quick Table calculation" based on the measure "Number of Records" expressed a "Percent of Total" computed using the field "IsUnknownVehicles" as shown below:

Sorry if my original post led to confusion. I hope the above makes sense. I'm sure I am missing the point somewhere but really can't figure it out. Thank you for your help.

Thierry

• ###### 4. Re: Sorting based on Quick Table Calculation (Percent of Total)

Hi All. Could someone be kind enough to help? This issue is driving me nuts...

Thanks a lot!

• ###### 5. Re: Sorting based on Quick Table Calculation (Percent of Total)

Hi Thierry

You'll need to single out the 'Known' ones to be able to sort on it.
See the attached workbook (pretend that prod category is your known/unknown ones)
* sheet 1: everything sorted on furniture % (could be your known %) --> see calculation1
* sheet 2: dynamic with parameter --> see calculation2

Hope that helps

• ###### 6. Re: Sorting based on Quick Table Calculation (Percent of Total)

Hi Tim,

Thank you for helping out. Unfortunately, my issue is different.

Taking your format as an example, I would like to be able to sort in ascending order the list of STATES based on % value of FURNITURES (for instance).

As you can see the screenshot below is sorting the list of STATES based on the NUMBER OF RECORDS. But what I need is to sort them based on the "% OF TOTAL NUMBER OF RECORDS".

If I click on the small sort button that appears when I hover on the horizontal axis, the sorting works. See below:

The problem is that such sorting is based on the % values at the time. When the database is updated, it won't automatically sort my view again.

Thank you

• ###### 7. Re: Sorting based on Quick Table Calculation (Percent of Total)

This issue is really driving me crazy.

Would really appreciate if someone could help me solving this...

THierry

• ###### 8. Re: Sorting based on Quick Table Calculation (Percent of Total)

I'm just about to run out of the door so can't play with this as much as I'd like, but here's the approach I'd take:

• Make a copy of your Table Calc (Sum([Number of Rows]))
• Convert it to Discrete
• Drop it in front of [State], where it will force-sort the State values

The issue I'm seeing is that I'm getting an ascending sort vs. a descending sort...but I have the "auto sort" behavior you're looking for....Will play with this more later in the afternoon (my time) if someone else hasn't figured it out by then:

1 of 1 people found this helpful
• ###### 9. Re: Sorting based on Quick Table Calculation (Percent of Total)

OK - Got it. See attached.

1 of 1 people found this helpful
• ###### 10. Re: Sorting based on Quick Table Calculation (Percent of Total)

Thanks a lot for helping out Russell.

Do I really need to use a parameter to make this work? I have a lot of views (using different dimensions) for which I would require this auto sorting and that would be a real pain to implement it everywhere. Also, I anticipate that the non dynamic nature of parameters may create issues.

Your first suggestion (duplicate calculcated field and converting it to discrete) is a nice and quick workaround but as you pointed out it will sort in ascending order. Any way to change it to descending order while keeping it as simple?

Thierry

• ###### 11. Re: Sorting based on Quick Table Calculation (Percent of Total)

Yeah - the sample I attached earlier demonstrates same. Just change the expression in the discrete calculated field to [whatever the expression was, I forgot] to ([whatever the expression was, I forgot] * -1 )

Hackolicious, but it works.

3 of 3 people found this helpful
• ###### 12. Re: Sorting based on Quick Table Calculation (Percent of Total)

Thank you Russel! Hope Tableau 8 will bring the ability to set this kind of sorting by default

• ###### 13. Re: Sorting based on Quick Table Calculation (Percent of Total)

Bother, if you were here right now I would hug you.  Sooooooooo many ranking formulas later and this simple solution does the trick.  THANK YOU!!