topic Ordering a Pivot table like a straight table in QlikView

Ordering a Pivot table like a straight table

Mon, 22 Jun 2009 12:07:03 GMT

I noticed in a straight table you can double click on a column and sort the whole table by that variable. is there any way to add this type of function to a pivot so it can be sorted by a particular variable?

Thanks

David

Ordering a Pivot table like a straight table

Anonymous — Mon, 22 Jun 2009 18:28:15 GMT

There is no standard way to do this.

One solution is to also make a straight table. You can then switch between the straight table (for sorting) and the pivot table (for hierarchical view).

If you realy want to make it look like you can change the sorting on a pivot table, you can alse do the following:

You can create 2 or more pivot tables that are exactly the same (also the same position!) except for the sorting. When you have created all the pivot tables (sortings) that you need, you need to set up a mechanism to show only one of your pivot tables at once.

There are a couple of different ways to switch between your pivot tables: 1 example (short overview): create a field (as data island) that you can use to select a sorting. You can then test in "Show Condition" of your different graphs when to show the wright graph. (make use of HidePrefix in your script for this field)

In QV 9 you can also try to do this with Actions

Ordering a Pivot table like a straight table

Anonymous — Mon, 22 Jun 2009 23:59:36 GMT

Just to add a little...
Pivot table is sorted by the order of its Dimensions, so there is no much flexibility if you don't want to move dimensions around. But keep in mind that a Dimension can be sorted by text ascending or descending, by frequenst, by expression, etc. So, to create a "dynamic sorting", you can use a macro button which accesses the pivot table properties and changes the sorting parameters.

Ordering a Pivot table like a straight table

Anonymous — Tue, 23 Jun 2009 03:40:30 GMT

Michael,

I haven't tried this solution because I try to avoid macro's whenever possible. But sometime macro's are indeed the best solution in QlikView.

Do you prefer the macro solution, or the solution I presented above? Does it perform well when working client/server?

I'm always intrested in other QlikView developers best practices.

Ordering a Pivot table like a straight table

Anonymous — Tue, 23 Jun 2009 03:47:31 GMT

Kris,
My "best practices" are:
1. Keep it simple
2. Avoid macros is possible (almost never so far ). Especially macros that change object properies. But sometimes they make life easier.
3. Do the way what is better for the client...
So, regaring your question, the answer (as it is often with QV) - it depends. Don't remember if I used this paricular (pivot sorting) macro in client/server, sorry.

Ordering a Pivot table like a straight table

Mon, 16 Aug 2010 03:35:38 GMT

Except that you can't sort a dimension using an expression (other than the 1st dimension)

Ordering a Pivot table like a straight table

Anonymous — Wed, 18 Aug 2010 13:42:09 GMT

Shortly after this discussion I uploaded this.
(Just happened so that I needed a solution for a client, and came up with this.)