Sort on different column of straight table

[Anonymous]

I have a straight table attached, It shows top 5 stores by POS Sales desc ranking at default.

Be advised, the attached excel file is just way I want to show in straight table not the data I want to directly use.

Here is what I want, when end user clicks different column header, the table need show top 5 stores by the clicked header accordingly. For instance, if POS Qty is clicked, the table shows stores ranked by POS QTY desc. NOT by POS Sales any more. therefore, it could be totally different store list there.

the confusing part is to show each column data, I have entered expression for each of them, How could I write additional expression and where I put? thanks.

[Gysbert_Wassenaar]

See attached example.

[Anonymous]

Thanks, Gysbert, I am using personal edition, could not open your file. Do you mind telling me the way you do? thanks.

[Gysbert_Wassenaar]

Option 1:

• Set the Max Number option on the Presentation tab to 5.
• Enable the option Allow Interactive Sort on the Sort tab.
• Sort the table by double-clicking on the column header by which you want to sort the table

Option 2:

Create seven variables

• vColumn; with inital value 1, used to store the number of the column that should be sorted
• vSortOrder; with inital value -1, used to store how to sort the colum: 1 for ascending, -1 for descending
• eSales; sum([POS Sales])
• eQty; sum([POS QTY])
• eInventory; sum(Inventory)
• eForecast; sum(Forecast)
• eSortExpression: pick($(vColumn), $(eSales),$(eQty),$(eInventory),$(eForecast))

Create a straight table with store# as dimension and five expressions:

1. HiddenSortColumn: $(eSortExpression)
2. POS Sales: $(eSales)
3. POS QTY: $(eQty)
4. Inventory: $(eInventory)
5. Forecast: $(eForecast)

On the Dimension Limits tab enable the option Restrict which values are displayed using the first expression. Enable the Show Only option en select Largest and 5 as settings for this option. Disable the option Show Others. The dimension limits are calculated based in the first expression. For this reason the first expression must be the HiddenSortColumn expression $(eSortExpression). This expression picks an expression based on the value of the vColumn variable.

On the sort tab move the store# column to the top of the priority list and enable Sort by Expression option for the store# column. Enter as expression: $(eSortExpression)*$(vSortOrder)

On the Presentation tab select the HiddenSortColumn column and enable the option Hide Column.

Create a new text box. On the Layout tab of the properties window set the Layer setting to Top. Resize and position the text box so it fits exactly over the header cell of the POS Sales column. Use the following expression for the Text value of the text box: ='POS Sales ' & if($(vColumn)=1, if($(vSortOrder)=-1, '▼' , '▲')).

Add a Set Variable action to the text box on the Actions tab. Use vColumn as Variable and 1 as Value.

Add another Set Variable action to the text box. Enter vSortOrder as Variable and use as Value this expression: =if($(vColumn)=1,-1,1)*$(vSortOrder)

Create three more text boxes for the other three expression header cells by copying the one just created. Change the Set Variable actions of these text boxes so the correct column numbers are used.