Sorting in a pivot table.

[rdkrauss]

I have a pivot table consisting of several levels (rows). In the second level, I have 3 values (lets call it town, state, country).  The default seems to be alphabetical.    I see you can control the sort by expression, numerically, or alphabetically.  How can I make it so it goes country, state, town.......Is there an expression I can use to force it into an order.  TIA

[MarioCenteno]

Try This

if(NAME='Country',1,if(NAME='State',2,if(NAME='City',3)))

[rdkrauss]

I put this in the Sorting section in the expression area.  The editor didn't like the expression.  Also tried replacing the name with the field name and that didn't work either

[MarioCenteno]

Can you share the qvf?

[rdkrauss]

Unfortunately not at this time.  will try to clean it in the next day.

[juraj_misina]

I suggest to make use of Dual() function. In the load script define your second level like this:

LOAD
Dim1,
Dual(Dim2, Match(Dim2, 'Country', 'State', 'City'))     as Dim2,
Dim3,
Meas1,
Meas2
from Source;

This will assign numeric values to Country (1), State (2) and City (3) and enable you to use numerical sorting.

Hope this helps.

Juraj