topic Re: pivot table sorting in App Development

pivot table sorting

stefano_marson — Thu, 25 Jul 2024 16:38:30 GMT

Hello Gurus,

On Qlik Sense on-premise version November 2023, I would need to set the row dimension sorting of a pivot table by the measure (sum(Sales)) calculated only on the maximum available Year. The column dimension (Year) on the other hand needs to maintain numerical sorting.

The option that allows sorting against the first measure of a pivot table only works if there is only one Year selected.

I also tried defining a first measure, to be used only for sorting, of the type:

sum({< Year={‘$(=max(Year))’} >} Sales)

but it doesn't work.

Notes:

- Dimensions in row can change position (e.g., Item before Store), but cannot be moved to columns.

- All combinations of dimensions in row have sales, i.e. sum(Sales) > 0, over all years

Any thoughts?

Thank you very much

Re: pivot table sorting

lennart_mo — Fri, 26 Jul 2024 08:07:49 GMT

Hi @stefano_marson,

under sorting there should be the option to switch to custom for every dimension. If you do so, you can add an expression to sort by. Your original expression wasn't all wrong except you didn't use double qoutes.

Try using this expression for your row dimensions:

=sum({< Year={"$(=max(Year))"} >} Sales)

Let me know, if this worked for you!

Re: pivot table sorting

stefano_marson — Fri, 26 Jul 2024 08:59:02 GMT

Hi lennart_mo,

I entered the expression

sum({< Year={"$(=max(Year))"} >} Sales)

for sorting each dimension but it only works with respect to the first dimension

Thank you

Re: pivot table sorting

lennart_mo — Fri, 26 Jul 2024 12:16:13 GMT

Hi Stefano,

It seems that using the expression in sorting Qlik disregards the structure of the pivot table.
In regards to your example table that results in the following:

Looking at the dimension departments as the second dimension:
Instead of calculating the sum for a distinct store, the total amount of sales by a department for all stores is calculated. This results in a sorting as if the department was the first dimension.

Unfortunately I haven't found a way to correct the sorting formula so far.