Using the dual() function to set the sort order of dimensions

The following recipe shows you a way to overcome a problem with the sort order when creating a range dimension. You create a calculated dimension to the group set of values as custom ranges and describe it as with labels such as "above 10", "< 30", "< 60", and "up to 100". If you sort it alphabetically, then it will not show in the same order. We can assign a numeric value to each label using the dual() function. Using this approach, you can simply set the sort order of the dimension as numerical and it will sort correctly.

Getting ready

For this recipe, we will generate sales data in the script, as defined in the following script. Load the following script into the Data load editor:

load round(rand()*100) as Value 
AutoGenerate(100);  

How to do it...

1. Go to the App overview window and open a new sheet.
2. Enter the Edit mode by clicking on
   
   .
3. Create a Table chart.
4. Add a new dimension, and click on the fx button to create it as an expression...