Sorting not working

[nikhilgarg]

Hello,

I have a Pivot table with dimensions:

Role, Region, Department, Username and 2 expressions as Quantity and Amount.

I am implementing the Sorting on 2 expressions.

Under Sort tab , for each dimension i checked expression in descending and used :

pick(match(_SortBy, 'Quantities', 'Amounts'),

     Quantity,

    Amount

     )

But Sorting is not working. Where am i wrong?

Thanks

[amit_saini]

Nikhil,

Try this:

Dual(Your_Field,Match(Your_Field, 'Quantities', 'Amounts'))

Thanks,
AS

[nikhilgarg]

Hey,Under Sort tab for each dimension i write expr as and selected descending:

Dual(Role,Match(_SortBy, 'Quantities', 'Amounts'))

Dual(Region,Match(_SortBy, 'Quantities', 'Amounts'))

Dual(Department,Match(_SortBy, 'Quantities', 'Amounts'))

Dual(UserName,Match(_SortBy, 'Quantities', 'Amounts'))

But still does not works

[sunny_talwar]

Would you be able to share a sample?

[nikhilgarg]

Plz found attached file . Also i am using personal edition so kindly letme know what changes i need to do here only.

Thanks

[preminqlik]

hi try this

pick(Match(_SortBy, 'Quantities', 'Amounts'),sum(Quantity),sum(Amount))

[sunny_talwar]

I think the problem is that this is a pivot table and it will need to break the pivot table's grouping to sort in the order you want.

But if you want you probably would be able to sort the way you want in a straight table

By using the following expression for just the UserName dimension:

=Pick(Match(_SortBy, 'Quantities', 'Amounts'), Sum(Quantity), Sum(Amount))

I hope this will help.

Best,

Sunny

[nikhilgarg]

Hey,

Thanks sunny but i want in pivot table. Cann't it be done in pivot anyway ?

Thanks

[sunny_talwar]

I may be wrong, but it seems that it will break the grouping. For example when you are sorting on Quantites your first and second row will be

Admin - India - Administration - O - 60

(Blank) - US - Administration - M - 56

Admin's are not all together anymore. If that's what you want, then you will end up seeing a straight table and I guess that's what the straight table is for.

So I doubt it is possible, but others may disagree

Best,

Sunny

[nikhilgarg]

The 1st row would be O-60

2nd would be C-6 (because it is in the same Role region and departent as of O)

3rd row would be M-56 and then the other values in the same department or role if any would get sorted

and so on.

Hope I'm clear

Thanks,

Nikhil