topic Re: Sorting on the pivot table in QlikView

Sorting on the pivot table

shree909 — Tue, 24 Mar 2015 20:46:05 GMT

Hi ,

I have an issue with the pivot table sorting. . i have Row fields which need to be arranged in the ascending order..

when i use that field in the expression of sort order its not working as expected.

Could someone look into this issue.

PFA the sample file.

Thanks

Re: Sorting on the pivot table

Anonymous — Tue, 24 Mar 2015 20:52:57 GMT

Your request contradicts the nature of the pivot table. Or, you have to move the column further to the left, before the rows start splitting.

Re: Sorting on the pivot table

hic — Tue, 24 Mar 2015 20:59:54 GMT

Not possible. And it is not a limitation of QlikView - it is a consequence of the properties of a pivot table.

A pivot table is always sorted first after the first dimension, then the second dimension, etc. This is necessary since the rows are grouped first after the first dimension, then the second dimension, etc. If you want to sort after an expression, you should use a straight table instead.

An other aspect is that you try to sort the "Document number" after "Row". But this is not possible since a document can have several row numbers. See also Use Aggregation Functions!

HIC

Re: Sorting on the pivot table

JonnyPoole — Tue, 24 Mar 2015 21:05:17 GMT

I found some unusual things in your example

like 'beginning balance' is a data value in the Month/Year dimension that otherwise has MMM-YYYY values

On the sort tab, i removed all sorts on all the first dimensions up to month/year which i switched to expression as follows:

if ( [Month/Year]='Beginning Balance',1,2)

Then i removed sorts on all subsequent sorts up to materialdocitem which has unique values (seemingly) where i put sort on Row

It seemed to 'survive' the need... subrows sorted within blocks etc... but please heed the wise advice above for doing this.

Re: Sorting on the pivot table

Gabriel — Tue, 24 Mar 2015 22:21:17 GMT

Hi,

May be this might help

Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

Re: Sorting on the pivot table

Anonymous — Tue, 24 Mar 2015 22:40:06 GMT

You can to try change pivot table to a straight table and Properties Tab Sort you will promote field row how on first place.

Re: Sorting on the pivot table

Anonymous — Wed, 25 Mar 2015 05:48:34 GMT

I totally Agree with henric

Re: Sorting on the pivot table

paolo_mapelli — Mon, 18 Dec 2017 09:49:31 GMT

Personally I totally disagree since this is something Excel does and I don't see a valid reason why Qlik Sense shouldn't do.

Re: Sorting on the pivot table

hic — Tue, 19 Dec 2017 17:35:46 GMT

paolo.mapelli‌

A pivot table is always sorted from left to right - that's part of the nature of the grouping of a pivot table. So then the last dimension can obviously not be sorted before the previous ones. The obvious alternative is to use a straight table. Then you lose the grouping, but can sort it any way you want.

An additional alternative is to use the pivot table, but promote the Row number so that it isn't the last dimension.

It sounds to me as if you want a pivot table that sometimes groups the values, and sometimes not, which would be illogical and counter-intuitive. Like the mock-up below where one single document number appears twice under the same date.

But I might be wrong, so could I ask you to elaborate on what you think Qlik Sense should be able to do, apart from what it already can?

HIC

Re: Sorting on the pivot table

paolo_mapelli — Wed, 20 Dec 2017 08:16:16 GMT

Generally speaking, ordering and grouping (IMHO) are two distinct concepts. For example in SQL you can GROUP BY, then ORDER BY.

As I wrote, I would like to have something that Excel does:

That is something you can have also in Sense if you order by AGGR(SUM(<measure>),<dim1>,<dim2>).

Of course there's the "limitation" to be a web application: visualization is different from elaboration, so if you drag <dim2> before <dim1> ordering isn't recalculated.

But many times pivot are useful just to have something that groups items by hierarchical dimensions and in those situations it'll be enough to have a "locked" pivot table, with undraggable dimensions.

So, getting back to your question: "what you think Qlik Sense should be able to do, apart from what it already can?"

Apart of what above (probably impossible since the "web nature" of Sense), a simple "Lock Table Dimensions" option in pivot table properties would be nice to have. In this way I would be able to setup a "fixed and hierarchical" pivot and order by a measure, with ordering always correct.

Paolo.