5 Replies Latest reply on Dec 28, 2012 10:18 AM by Tracy Rodgers

# Give a parameter control for ascending descending sort (order along) on advanced table calculation

Hi Everyone,

I need to expose the ascending/descending sort option of the advanced table calculation to users of my workbook. The highlighted section in the image below is what I'm referring to:

Does anyone know how to pull off this trick?

Thank you.

• ###### 1. Re: Give a parameter control for ascending descending sort (order along) on advanced table calculation

Hi John,

Is Sales the only measure on the view?

-Tracy

• ###### 2. Re: Give a parameter control for ascending descending sort (order along) on advanced table calculation

Sort it by a calculation which is Sum([Sales])*[parameter], where the parameter has list values of 1 or -1. You can change the display as to be something meaningful.

• ###### 3. Re: Give a parameter control for ascending descending sort (order along) on advanced table calculation

Hi Tracy,

Thanks for jumping in!

I have 3 measures, one that shows the value from the previous period, one showing the current value, and a column showing the delta from the current period compared to the previous period. Each of those measures is showed under a given quarter. Typically, there are about 4 quarter's worth of data to display.

The sort I want to happen takes place against the delta measure. The user selects which quarter to sort by based on the input from a parameter. I would love to give the user the ability to sort ascending or descending on the delta measure. Currently, I'm having to create an ascending and a descending sheet for each, and that's no fun at all!

Thanks!

• ###### 4. Re: Give a parameter control for ascending descending sort (order along) on advanced table calculation

Thanks Alex, I appreciate the advice.

I'll give that a try. I can't see why that wouldn't work, and I love the simplicity and elegance of your approach.

If/When it works, I'll give you a green star!

Take care,

John.

• ###### 5. Re: Give a parameter control for ascending descending sort (order along) on advanced table calculation

Hi John,

I noticed you opened a case with our Support team and they provided you with an answer. I wanted to share that here in case anyone has a similar question!

I have attached a sample packaged workbook (Example.twbx) that demonstrates how to use a parameter and Calculated Fields to swap out a Table Calculation that is identical other than the descending or ascending ordering in the Compute Using settings. The steps to recreate the workbook are as follows:

Step 1: Turn the Quick Table Calculations into Calculated Fields and set the Advanced Compute Using settings

1. In order to turn Quick Table Calculations into Calculated Fields, follow the steps in the attached Word document.

2. With the Calculated Fields in the view, set the Compute Using settings as desired. In this scenario, all settings should be identical apart from Ascending and Descending Sum of Sales.

Step 2: Create a parameter to swap out the measures