Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Sorting on the pivot table

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.

Issue1.png

PFA the sample file.

Thanks

@

9 Replies
Highlighted
Champion III
Champion III

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.

Highlighted

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

Highlighted
Employee
Employee

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

Capture.PNG

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

Capture2.PNG

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

Highlighted
Partner
Partner

Highlighted
Specialist
Specialist

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

1.2.PNG

Highlighted
Creator III
Creator III

I totally Agree with henric

Highlighted
Creator II
Creator II

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

Highlighted

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.


QlikSense.png


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


AltPivot.png


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.


QuirkyPivot.png


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

Highlighted
Creator II
Creator II

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:

Clipboard01.jpg

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.