Pivot table sorting not working

[ankitaag]

Hello All,

I am attaching a sample application where I want the user to select any 1 measure from the listbox and the pivot table should get sorted accordingly.

Please help!

morganaaron‌

Thanks and Regards,

Ankita

[timanshu]

Hi Ankita,

Swuehl is right.

when sorting role, it sums up amount of  each role independent of  Region and department.

For sorting to work, do this:

Replace Department sorting expression to :

Pick(

  match(Sort,'Quantity','Amount'),

  aggr(sum(Quantity)  ,Region),

  aggr(sum(Amount)  ,Region)

)

and Role sorting expression to :

Pick(

  match(Sort,'Quantity','Amount'),

  aggr(sum(Quantity)  ,Region,Department),

  aggr(sum(Amount)  ,Region,Department)

)

similarly add Role for user  sorting expression

[ankitaag]

Thankyou for your reply Timanshu,

Though it is still not working. Lol

[ankitaag]

Hi, Please find attached the app as per your solution.

[timanshu]

Hi,

So now where is sorting wrong. I found the report correctly sorted in attached app.

[swuehl]

Not sure what Ankita wants to demonstrate with this sample.

It's different from the original by the way dimension values are related to higher dimensions. For example, each user is related to only one single role. But the sorting issue will only be visible if you have a 1:n relation, e.g. a user is related to more than one role. In Ankita's original post, the empty or blank role shows this issue because it's related to many departments.

Timanshu, I don't think your solution will work. Try with a simple sample I've attached (try to sort Field2).

[timanshu]

Hi Swuehl,

Yes It does not work for the case you provided. I think , Pivot table sorts like a straight table and it does not consider grouping while sorting so that  values under different groups sort  in different way.

So , any way that could make sorting work in this case?