Unable to sort dimensions in row in pivot table by measure value

[theqlikguy]

Hi all.

I have this sample data

I want to create a pivot table with dimensions parent and child in row and year in columns and measure sum(Value), and then order the value of parent and child dimensions by measure (total of the years) desc.

The desired result is like excel

Y is the first under B but is the second after A.

I can't able to reach this reasult in qlik apparently: Y remain in the first place under A.

In the sort options I set custom sort by expression for both parent and child with expression sum(Value).

Disabled option "order by first measure".

Y is in the first place if I use child as first dimension of the hierarchy, so I think that it is not ordering correct for the couple of values, so I tried also forcing the context in the sort expression with sum(total <Parent,Child> Value) but nothing change.

Any help?

Thank you

[Chanty4u]

Try this in sort expression 

Aggr(

    Sum(TOTAL <Parent,Child> Value),

    Parent,

    Child

)

[MatheusC · Accepted solution]

I saved this solution, credits to @rubenmarin 

https://community.qlik.com/t5/App-Development/Sorting-2nd-dimension-Issue/td-p/1558354

In your child dimension:

Child = Aggr(Dual(Child, RowNo(TOTAL)), Parent, Child)

And in the classification tab, enter your measure.

- Regards, Matheus

[theqlikguy]

Ok, it works, thanks.

If I need to do the same on more than two dimensions, I think that I can do the following:

Aggr(Dual(third dim, RowNo(TOTAL)), Parent, Child, third dim)

and so on, correct?