Sorting on the pivot table

[shree909]

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

@

[Anonymous]

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.

[hic]

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

[JonnyPoole]

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.

[Gabriel]

Hi,

May be this might help

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

[Anonymous]

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

[Anonymous]

I totally Agree with henric

[paolo_mapelli]

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

[hic]

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

[paolo_mapelli]

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.