Sorting within a dimension in three dimension pivot table

[#1]

Hi.  I have what I though was a simple problem until I went down the rabbit hole!.

I have a table below (sample data):

What I need to do is sort by the first Qty value.  Ie Qty=1.  Therefore the order for SL should be: Zane John Peter, or Zane Peter John, and the sort for AL should be John Peter.

I though this would be simple by setting the sort for the person dimension to Sum(if(qty=1, qty))

Ie, only include values where the qty dimension is '1'

But it doesn't work.

Can anyone offer a suggestion.

Thanks in advance (sample QVW attached).

[#2]

Hi,

you can use "Ord(expression)"  in sort tab Expression,

Then you can get the correct order

[#3]

In pivot table, there is no direct option for expression sort. In sort tab, for type and person dimension,add your Quantity expression in expression option and make it as descending.

Hope this will help you...!!!

[#4]

Thank you for your prompt replies.  But I don't think you understand the issue.  I need to sort the person dimension based on only the contents of the Qty dimension for a value of 1.

If you are able to do this, can you please make changes to the sample file to demonstrate.

Thanks.

[Josh_Good]

Try sorting the person dimension using the expression:

=Sum({$<Qty = {1}>}Qty)

in Ascending order

[Josh_Good]

Thinking on this further.  My solution only worked because of a quirk in your data.  I don't believe it is possible to have a different sort order for a dimension nested inside another dimension.  Zane keeps coming when sorting in descending order because he has the least qty for Qty = 1 overall.