12 Replies Latest reply on Jun 26, 2012 5:44 PM by Michael Mixon

# Sorting based on table calculation

Hi,

I've done some searching already for this, and it appears it may not be possible, but I'd like to give the big minds on this forum a chance.  Here's what I want to do:

• I have a forecast accuracy metric that I calculate for each week of the quarter.
• I need to display a quarterly average of all of those weekly numbers.
• I need to sort the resulting chart by the quarterly average and assign ranks to the customers based on this quarterly average

Creating the quarterly average is simple (window_avg([Forecast Accuracy])), and displaying the quarterly averages by customer is also easy (I just place week on the level of detail shelf and turn off stack marks so that the label shows a single value for each customer.

The difficult part is getting the resulting chart to sort in descending order of this quarterly average.  Whenever I try to do it (using the quick sort), I get inconsistent and often incorrect results.  Furthermore, I need to have the list re-sort dynamically when a user selects a different product segment.

Is this possible?  Please make my week by saying 'Yes'.

-Mike

• ###### 1. Re: Sorting based on table calculation

Maybe. But you'll need to post a sample workbook so we can tinker with it. Sounds like you may be able to do it with a set sort. But can't really tell without the workbook.

--Shawn

(medium mind)

• ###### 2. Re: Sorting based on table calculation

Like Shawn said, a sample workbook would be super-helpful. As you've seen, table calculations are tricky to work with and settings have a lot of variation based on your desired outcome(s).

If your case is simple, the following might work: Tableau doesn't offer a custom default sort on dimensions based on table calcs. However, you can take advantage of Tableau's default alphanumeric sort, by creating a discrete field that would sort the way you want and using that as the left-most field in the view. In your case, you could create a calculated field with the formula: -[your quarterly average field]. Drag that to the Rows shelf, set it to Discrete, and put it as the left most column, then turn off Show Header.

Jonathan

• ###### 3. Re: Sorting based on table calculation

I can definitely put together a sanitized workbook for you to review.  I'm using version 6.0.8.  Is that going to be okay?

-Mike

• ###### 4. Re: Sorting based on table calculation

Hi Mike,

It won't be an issue for me!

-Tracy

• ###### 5. Re: Sorting based on table calculation

I'm attaching a sanitized version of the dashboard I'm working on.  I'm eager to see what suggestions you come up with. :-)

-Mike

• ###### 6. Re: Sorting based on table calculation

Mike,

Thank you for providing a workbook.

As for sorting the view by the result of a table calculation, that can be achieved by placing the table calc pill as a discrete pill. If you would like to sort descending, place a negative sign in from of it, likely as another calc field.

I made a few other adjustments, using some different methods, that will help with performance as your data grows larger. As with anything in Tableau there are multiple ways to accomplish the same results, the attached route would be my preference for this situation, and likely different if there were other constraints.

Here is the link to it on Tableau Public:

http://public.tableausoftware.com/views/SanitizedForecastAccuracyDashboardjmedit/FcstAccuracySummaryjmedit

Here are the steps to reproduce:

1. change the formula for "Forecast Accuracy (Window Avg)" to:

IF LAST()==0 THEN

WINDOW_AVG([Forecast Accuracy],IIF(LAST()==0,FIRST(),0),0)

END

2. change the formula for "Latest FW" to:

LOOKUP(MIN([Fiscal Week]),0)=[Latest Fiscal Week]

3. add a calc field, "Sort" with the formula (once created, right-click and "Convert to Discrete"):

-[Forecast Accuracy Window Avg)]

4. remove the pill "Latest FW" from the rows shelf (it likely has a red exclamation mark)

5. place "Latest FW" on the filter shelf, keeping only when True (set the compute using to "Fiscal Week" for the pill, may need to set filter twice)

6. set the compute using for the two "Forecast Accuracy (Window Avg)" pills to "Fiscal Week"

7. you can move the pill for "Fiscal Week" on to the Level of Detail shelf (not necessary, just makes for less clutter on the Rows shelf)

8. place "Sort" as the first pill on the Rows shelf, and from its context menu, un-check "Show Header"

your result will look something like:

Message was edited by: Joe Mako - edited step 5 - published to Public

• ###### 7. Re: Sorting based on table calculation

Hi Joe,

Thanks for the thorough reply.  I was able to re-create some of your steps, but ran into some issues (e.g. the new Latest FW field, when placed on the filter shelf, only gives me the value of False as a selection option).  However, when I tried opening the workbook in Tableau Public to see under the hood, I got the following error:

The requested workbook does not exist on Tableau Public.  Only workbooks that are currently available on Tableau Public may be opened.

Any ideas?

-Mike

• ###### 8. Re: Sorting based on table calculation

edited the steps to include what I left out, and published to Public

• ###### 9. Re: Sorting based on table calculation

Hi Joe,

This might be a stupid question, but where would I find it?

• ###### 10. Re: Sorting based on table calculation

find what? If you mean what I published to Public, you can find the link in my comment above.

• ###### 11. Re: Sorting based on table calculation

Ahh...I'm blind.  I missed the link in your earlier post.  I'll take a look at it tomorrow.  Thanks again.

• ###### 12. Re: Sorting based on table calculation

Cool.  This worked.  I swear I tried this before and it didn't work.  I guess it just needed the Mako touch. :-)  Thanks again, Joe.

-Mike