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:
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.
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:
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?