Pivot Table - Sort with expression - Dimension Value

[jerryr125]

Hi -

I am using the Pivot Table Visualization.

I am using a column dimension  - "Category Name".

I would like to sort the column dimension in a specific order.

I created a sorting - by expression using the following:

Pick([invcatid],'ma','cg','sg','hs')

(the 'ma','cg','sg','ms' is the only inventory categories valid).

It does not sort accordingly - any thoughts ? Thanks - Jerry

[Seyko · Accepted solution]

Hello Jerry,

You can modify the default order of values on this field directly in the script editor :

•  Create an inline table which contains the values in the order intended

// Values with desired order 
TMP:
LOAD * INLINE [
invcatid
ma
cg
sg
hs];

// Here, we've your final table which contains concerned dimensions
MyTable:
LOAD
invcatid,
*
RESIDENT TAB;

// Drop table
DROP TABLE TMP;​

• After that, the field invcatid take the 'good' order. Now, you can use this field in sort part (by expression). 

Tell us if it works.

Regards