topic Re: Sorting of a pivot table (different sorting if two dimensions) in QlikView

Sorting of a pivot table (different sorting if two dimensions)

Fri, 20 May 2011 13:25:46 GMT

I have a pivot table with two dimensions and two formulas:

First dimension: Item Category

Second dimension: Customer

First formula: Sum(Sales)

Second formula: Sum(Quantity)

The sorting of the first dimension should be by loading order (or alphabetical).

The sorting of the second dimension should be by the first formula.

What I tried:

1) "Sort by y-Value": Sorts both dimensions by Sum(Sales) => Not what I want.

2) "Sort by Expression" for the second dimension: "Sum(Sales)" => Sorts the customers depending on there total Sales disregarding the first dimension => Again not what I want.

Question: Is there a way to tell QV that the "sort by expression" should consider the first dimension? Or is that what I want just something QV can't handle?

Thanks in advance and regards

Alex

Re: Sorting of a pivot table (different sorting if two dimensions)

nagaiank — Fri, 20 May 2011 14:07:18 GMT

An example with different sort order for the two dimensions ia attached. Is this what you want to do?

Re: Sorting of a pivot table (different sorting if two dimensions)

Fri, 20 May 2011 14:15:28 GMT

Your example describes the problem but unfortunalety not the solution

Customer B has the highest Sales overall (900 + 700 = 1600). That's why he is in first place for both Categories.

What I want is the following:

1 - A - 1100

1 - B - 900

1 - C - 750

2 - B - 700

2 - C - 500

2 - A - 400

the customers should be sorted by sales for each category different.

Re: Sorting of a pivot table (different sorting if two dimensions)

nagaiank — Fri, 20 May 2011 14:32:35 GMT

According to QlikView Reference manual, 'Pivot tables can only be sorted by some property of the first column.'