Pivot Table Sort Issue

[#1]

I have an issue whereby I have created a sort order 1-7 and applied it to a field, but when the sum of that field is 0, the sort order does not apply.

Below demonstrates the correct sort order (see Tax in position 6).

When a different month is selected and Tax has a 'Actual' result of 0, it is automatically placed at the top of the group list.

How can I maintain this sort order at all times?

[#2 · Accepted solution]

I found the solution,

In the script I applied a dual() command to the 'Group' field to give the same field a text and numeric value.

E.g.

LOAD Dual(Group,GroupSort) as Group

I then sorted the Pivot Table by Numeric Value instead of expression and voila! It works perfectly.

[suniljain]

LOAD Dual(Group,GroupSort) as Group

what value you taken for GroupSort