Pivot table: sorting by expression

[simone_g]

Hello!

I have a pivot table with two dimensions: Product and Nation. I need to calculate total sales per Product and Nation. For each product, nations should be sorted in decreasing order by expression:

Please see the attachment. How should I change the pivot table to get the desired sorting?

Thank you in advance!

[Or]

You would need to set the second dimension to a custom sort and use an expression (descending) for sorting:

=Aggr(Sum(Sales),Nation)

Note that this will be hard-coded to the dimension and measure in question, so it won't work if you use cyclic groups, dynamic pivots, or anything of that nature.

[simone_g]

I tried your expression for sorting but I am not getting the desired order.

Are you sure the expression works? Am I missing something?

[Or]

Assuming your measure is Sum(Sales) and your dimension is Nation, it should work (though you should remove the Stato checkbox first). If your measure is something else, this should match your measure.

[simone_g]

Actually I have two dimensions: Product and Nation.

I removed the Stato checkbox but it still does not work.

[Or]

Ah, I neglected to include Product.

=Aggr(Sum(Sales),Product,Nation)

[simone_g]

Unfortunately, it still does not work. See attached document.

My measure is Sum(Sales). Nations are ordered in the same way for each Product. Nations are ordered according to overall sales regardless of the product.

[Or]

What is the sort order for Product? It should be set to either numeric or text (or both).

[simone_g]

It is set to numeric, increasing.

[Or]

I'm probably missing something obvious here, but you are correct in that it does not work as expected.

As a workaround, you can use a calculated dimension with this sort order. Otherwise, perhaps someone else can figure out what I'm overlooking and fix it with just the sort order.