Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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
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.