- 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
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:
- HiddenSortColumn: $(eSortExpression)
- POS Sales: $(eSales)
- POS QTY: $(eQty)
- Inventory: $(eInventory)
- 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.