Discussion Board for collaboration related to QlikView App Development.
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.
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!
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.
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?
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.