Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try This
if(NAME='Country',1,if(NAME='State',2,if(NAME='City',3)))
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
Can you share the qvf?
Unfortunately not at this time. will try to clean it in the next day.
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