topic Double sorting in Pivot Table in QlikView

Double sorting in Pivot Table

Mon, 08 Nov 2010 19:12:51 GMT

Hi,

I would like to sort two dimensions with expression in a pivot table.

I've 3 dimensions : Country, Product, Year and 1 expression Sum(Sales).

Country I Product I Year 2010 I Year 2009.

I would like to sort Country by Year 2010 (sum({<Year={2010}>] and sort Product by Year2010.

The sort by country is Ok but it doesn't sort by Product.

Can you help me ?

Double sorting in Pivot Table

pover — Mon, 08 Nov 2010 19:34:52 GMT

Assuming you are using the sort by expression in the Sort tab and that the expression is sum({$<Year={2010}>} Sales) for both dimensions then I don't see a problem with this.

Post a screenshot of what the table looks like.

Regards.

Double sorting in Pivot Table

Mon, 08 Nov 2010 20:42:54 GMT

Yes it is exactly what I've done but the sort is not good on the Product. It only work on the first Dimension

Double sorting in Pivot Table

Mon, 08 Nov 2010 21:05:28 GMT

It works as it should. See the attachment. Your products are sorted by the expression in descending order.

Double sorting in Pivot Table

johnw — Mon, 08 Nov 2010 21:17:51 GMT

I gather you want your Product list to be sorted differently for each Country? Pivot tables currently do not sort that way. Every Country will have the Product list sorted the same. The product list IS sorted by sum({<Annee={'2010'}>} CA), but for the total across all countries, not within each country.

There is an open request to add an option to QlikView to allow it to sort in context. In your case, such an option would allow you to sort differently by Product within each Country. So far as I know, there is no way to do it presently. I do not know if there is any new option in version 10. I'm on version 9 SR6.

Here's the link to the request on the customer portal. If you have access to it, you could vote for it, and perhaps they'll address it some day.

https://emea.salesforce.com/ideas/viewIdea.apexp?id=087200000008T02

Double sorting in Pivot Table

Mon, 08 Nov 2010 21:23:04 GMT

Product are sorted by Sum(Sales) for Year 2010.

But i would like to sort product for each country : Product 100 should be first in country 2 and Third in country 1.

How can i do that ?

Thanks

Double sorting in Pivot Table

Mon, 08 Nov 2010 21:27:08 GMT

Ok thanks John.

Double sorting in Pivot Table

Mon, 08 Nov 2010 21:32:46 GMT

John,

It still doesn't work in version 10 either (10.00.8715.5 IR)

Double sorting in Pivot Table

Mon, 08 Nov 2010 21:36:21 GMT

But it already works in your dashboard if you would treat it in appropriate way. All what you need is just pick one country only. See attachment. I've updated it a little bit 🙂

Double sorting in Pivot Table

pover — Mon, 08 Nov 2010 21:40:23 GMT

You can do it if you make an expression for each year and sort by the y-value like the attached example.

Regards.

Double sorting in Pivot Table

johnw — Mon, 08 Nov 2010 22:04:54 GMT


Karl Pover wrote:You can do it if you make an expression for each year and sort by the y-value like the attached example.

Huh. I would not have expected that to fix the problem. I'll need to keep that in mind.

Double sorting in Pivot Table

Mon, 08 Nov 2010 22:09:49 GMT

Karl, i'll use your solution.

Thanks to all of you.

Double sorting in Pivot Table

Thu, 11 Nov 2010 19:25:59 GMT

John,

Am I wrong or is this not also true of straight tables?

I posted my problem earlier and Nick Bor directed me to this post.

This is a basic list function that really needs to be supported. How then can we rank major dimension groups and the rank minor dimension groups within the major group?

Rich

Double sorting in Pivot Table

johnw — Thu, 11 Nov 2010 20:08:08 GMT


Richard DeRocco wrote:Am I wrong or is this not also true of straight tables?

If I understand your question, no, it's not true of straight tables. To demonstrate this to yourself, just pull up the testExpressionSort.qvw that Nick posted. Convert the top chart into a straight table. Double click on Sales, then double click on Country, then double click on year. The products are now sorted differently within the context of the other dimensions.

With the pivot table, you can only sort by the dimensions, not by dimensions OR expressions. You CAN enter an expression to sort by, but that expression is always evaluated globally. There is no option to only evaluate that expression in context.

This is making me think, though, that the solution might be simpler than a "sort in context?" checkbox or the like. Now it seems like all we need to do is use the straight table version of sorting. In other words, put the expressions on the sort tab too, not just the dimensions. I haven't thought that through. They may be excluded for something having to do with our ability to put dimensions on either the left or on top. Not sure.

Double sorting in Pivot Table

Thu, 11 Nov 2010 20:28:36 GMT

Actually John the problem that lead me here was a bit different:

I'm working on a complex chart involving multiple dimensions and sorting within those dimensions. I was getting strange sequence results.

So I did a test on a very simple chart and was able to duplicate the problem in a simple pivot table with two dimensions and one expression -- sum (Sales).

I'm sorting dimension 1 using the expression -- SUM (TOTAL <DIMENSION1> Sales) works fine

I've tried several different expressions for the sort of dimension 2 including --SUM(TOTAL <DIMENSION1, DIMENSION2> (Sales) yielding the same results but for simplicity I'll stick with -- SUM (Sales) for the expression sort of Dimension 2.

In short I'm trying to sort the chart first by the decending value of sales for dimension 1 and then sorting Dimension 2 decending within the sorted dimension 1

When I make no selection in Dimension1 occasionally some of the rows of dimension 2 are out of the decending value sequence within Dimension1. If I make a selection of dimension 1 the results are correctly sorted for dimension 2. I've also gone back and changed my chart to a straight table with the same results. I can't get my head around why that is.