How to create a custom sorting in Dimension?

[#1]

Hi all,

I have a dimension called Region, for example, it contains values like London, New York, Hong Kong, Japan, Singapore, Australia ..... etc

I would like to show a table with the first Dimension as Region with a custom sorting. It should show first as London, second as Hong Kong, third as New York, for the remaining order it should sort in ascending alpahbetical order from A to Z.

The possible values of Region is not constant, it could be expanding with more countries adding into the Region list.

Any suggestion how to archive it?

Thank you !

[fernandotoledo]

is your custom sortin static (always the same)?

If so, you can load an auxiliary table with the order you want:

Country, CountryOrder
Brazil, 1
Argentina, 2
USA, 3

then on the "sort" panel you select to sort by expression and use the expression =Only(CountryOrder)

[marcel_olmo]

So, you mean that you want these cities on the top: London, second as Hong Kong, third as New York. And the other ones in alphabetical order?

Maybe if you create a flag like 'orderfield' and you put for example London -> OrderField 1. Hong Kong -->OrderField 2, NewYork --> OrderField :3. And the other ones put orderField : (big number like 99), and sort first for orderField, and the second parameter the alphabetical order.

Hope it helps!

[#4]

Look at the "Dual" functionality and use it to load a small table similar to the method that Fernado suggested. The dual functionality is for data items that have a dual nature - both a text value and a numeric value. The text value will show up in the chart dimension but the numeric value can be used for sorting.

[johnw]

You could take advantage of the load order sort and do something like this at the top of the script:

RegionSort:
LOAD * INLINE [
Region
London
Hong Kong
New York
];
CONCATENATE (RegionSort)
LOAD DISTINCT Region
FROM wherever
ORDER BY Region
;

Then drop the table at the end of the script. I tend to mostly use load order when I need custom sort orders these days.