Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Sort dimension by expression in a pivot table

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.

6 Replies
ziadm
Valued Contributor

Re: Sort dimension by expression in a pivot table

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
Honored Contributor II

Re: Sort dimension by expression in a pivot table

can u upload a sample qvw?

Not applicable

Re: Sort dimension by expression in a pivot table

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.

Highlighted
Not applicable

Re: Sort dimension by expression in a pivot table

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
Honored Contributor

Re: Sort dimension by expression in a pivot table

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
New Contributor

Re: Sort dimension by expression in a pivot table

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.