topic Re: Pivot table: sorting by expression in QlikView

Pivot table: sorting by expression

simone_g — Tue, 16 May 2023 09:02:53 GMT

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!

Re: Pivot table: sorting by expression

Or — Tue, 16 May 2023 09:37:43 GMT

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.

Re: Pivot table: sorting by expression

simone_g — Tue, 16 May 2023 10:20:07 GMT

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

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

Re: Pivot table: sorting by expression

Or — Tue, 16 May 2023 11:49:43 GMT

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.

Re: Pivot table: sorting by expression

simone_g — Tue, 16 May 2023 12:01:03 GMT

Actually I have two dimensions: Product and Nation.

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

Re: Pivot table: sorting by expression

Or — Tue, 16 May 2023 12:02:15 GMT

Ah, I neglected to include Product.

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

Re: Pivot table: sorting by expression

simone_g — Tue, 16 May 2023 12:14:34 GMT

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.

Re: Pivot table: sorting by expression

Or — Tue, 16 May 2023 12:44:33 GMT

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

Re: Pivot table: sorting by expression

simone_g — Tue, 16 May 2023 12:50:54 GMT

It is set to numeric, increasing.

Re: Pivot table: sorting by expression

Or — Tue, 16 May 2023 13:05:43 GMT

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.

Re: Pivot table: sorting by expression

simone_g — Tue, 16 May 2023 16:05:25 GMT

Thank you.

I tried your expression with Dual but it creates duplicates. For example, I get 2 rows for France for product 2. Moreover, there is no row for Germany. I am not sure how I shoud sort the calculated dimension in the sorting menu.

Re: Pivot table: sorting by expression

Kushal_Chawda — Tue, 16 May 2023 23:35:08 GMT

@simone_g Pivot table sorting works on individual dimension, unfortunately there is no option to sort it by measure which is why sorting by expression also doesn't work as expected when you have multiple dimension. Hence, other option is to use calculated dimension as mentioned by @Or you can try below calculated dimension

=dual(Nation, aggr(sum(aggr(NODISTINCT rank(Product,4,1),Product)*1e5)+sum(Sales), Product,Nation))

then sort your calculated dimension by Numeric descending

Re: Pivot table: sorting by expression

vamsee — Wed, 17 May 2023 03:58:07 GMT

Hi Simone,

PFA, use the dual while loading data. -Doesn't make sense logically but does the trick.