Sort dimension by expression in a pivot table

[#1]

Hello there,

after spending almost an entire day I realize that I'm stuck and can't solve this problem on my own by googling etc. I'm fairly new to Qlikview and for confidentiality reasons I can't share my file. I'll try to explain as much as possible to give a clear picture of what I want to achieve.

I have a pivot table with 3 dimensions and 6 expressions. The table is meant to help visualize what we sell, how much and to whom.

From left to right:

Dimensions: Customer, Product family, Material Name

Expressions: Quantity YTD, Quantity last year, Quantity two years ago, Total Price YTD, Total Price last year and Total price two years ago.

I would like to sort the information in the dimensions by the following order:

Biggest customers first, Product family in alphabetical order and Material name according to last years top grossing material on top.

The formula that's used in the expression "Total Price last year" is:

=sum({$<[Fiscal year]={"$(=(max([Fiscal year])-1))"}>}[Net Invoice Price])

The standard sort functions obviously doesn't work for dimension 1 and 3. As a beginner, I've tried playing around with the AGGR() function. I've successfully managed to sort the first dimension by the following expression.

=aggr(sum({$<[Fiscal year]={"$(=(max([Fiscal year])-1))"}>}[Net Invoice Price]),[Customer Name])

The second dimension could be solved by the standard sort functions, but the last one I can't seem to solve. I assumed it would be like the first dimension, but it wasn't. It doesn't work if I try and sort the Dimension with Material Name by the following expression:

=aggr(sum({$<[Fiscal year]={"$(=(max([Fiscal year])-1))"}>}[Net Invoice Price]),[Customer Name],[Product Family],[Material])

It sorts somehow but I can't figure out the logic behind.

But if I make a new dimension from the exact same expression and put it before Material Name I can easily sort it by the standard functions. However as it is a pivot table I can't hide this column and it looks really bad to have it in there.

So, could anybody advice me how to adapt the sorting formula in the dimension with Material Name?

What am I doing wrong?

Thank you very much in advance.

[ziadm]

Hi

check this on Pivot Table sorting by expression

Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

[Frank_Hartmann]

can u upload a sample qvw?

[#4]

Hi and thanks for the link. Looks interesting but not really understandable to me yet. I'll look through it again later and see if I can make some sense out of it.

[#5]

Hey there,

sorry I can't share the file and I'm not competent enough to set up a demo environment for test purposes.

[effinty2112]

Hi Hampus,

In the sort tab of the pivot table set the orders like this:

1st dimension Customer. Sort by an expression such as Sum(Sales) or whatever expression you would use to determine your "biggest customer". Set sort order to descending.

2nd dimension Product family. Sort by Text A -> Z.

3rd dimension Material Name.  Sort by an expression such as Sum({$<Year = {$(=-1+max(Year))}>} Sales).

You will need to replace Year and Sales with whatever the relevant terms are in your data model. Set sort order to descending.

Good luck

Andrew

[ekademeteme]

I had a same scenario where i had 3 dimensions in my pivot table and 8 measures. All dimensions where Row Dimensions. my sorted was supposed be on the 3rd inside dimension, so i had to Sort the 1st dimension by Y-Value, the second dimension by Text, and the last dimension by Value, thats how i resolved my issue.