Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot table sort


Hi all,

In my load script a column 'HashVal' is calculated to define the ordinal position of the rows.

In a straight table I can define a primary sort on 'HashVal' (ascending) and it renders correctly like this:

straight.png

But I can't seem to get it to appear in the correct sequence in a pivot table (fast type change):

pivot.png

for example 2058 should appear before 2059, 2062 before 2063 etc.

I've tried unchecking all the sort values in properties and then using expression min(HashVal) for combinations of the dimensions but to no avail.

I've only included HashVal for visibility - I don't actually want to see it in the pivot table (nor want a straight table option).

Can anyone help please?

Regards,

Gordon

v11sr8

13 Replies
Anonymous
Not applicable
Author

Gordon

Looks like an extra dimension called say Level0 would help.

In the attached qvw I have frigged this as a Calculated Dimension with expression :

=if ( match(Level1,'Cases','Claims','Recalls','Maintenance Plans') , 'Places' ,

if ( match(Level1, 'Journals' ) , 'Journals' ,

if ( match(Level1, 'Reports' ) , 'Reports'  ,

'The Rest'

) ) )

Calculated Dimensions are not something I would recommend, but you could sort this Level0 creation in your load script.

Other things could also be tidied in the load script like after doing the above have a preceding load with something like :

     if ( Level0 = Level1 , Level2 , Level1 )     as Level1 ,

To avoid Level0 & Level1 displaying as the same, by putting the Level2 value in Level1.  This would need similarly need sort for the other levels.

Bill

kuba_michalik
Partner - Specialist
Partner - Specialist

So, the mis-sorted rows all have null dimension values, right? Or are those actual values (empty strings, spaces)?

Because if they are null values, then maybe I'm crazy, but try switching the pivot table to show subtotals on top. Yeah, I know you don't have any.

kuba_michalik
Partner - Specialist
Partner - Specialist

OK, the above is irrelevant, I haven't checked the example qvw first, sorry. I think it's an issue I have encountered before. Dimension sort order does not depend on pivot table context. Put a listbox somewhere, with for example Level2, and sort it by Min(HashVal). The order you get here will be preserved in pivot table no matter what, if you use the same sort expression for dimension in pivot table. So for example, Recalls will always be above Maintenance, even if for Level1=Setup it should actually be below.

My workaround was rather hacky. In script, I have appended a number of spaces to each subsequent occurence of a same value in a single hierarchy level (first occurence as is, second + one space, third + two spaces, etc.) That way, they displayed exactly the same (trailing spaces are not shown - you don't get the ... because of them), but the values were actually distinct and could be sorted the way I wanted. In case you want to allow selection on those fields and do not want to see duplicates in listboxes, associated fields without the spaces can be kept in the data model.

Not applicable
Author

Hi Bill and Jakub,

Thanks very much for your responses - sorry for my late reply.

I just couldnt get the pivot table to respond to a sort sequence - it always seemed to want to render in 'load order' which meant of course that any value previously loaded would appear out of sequence when it appeared on subsequent occasions.

My 'solution' was to force a number of spaces on the end of 'Item' to make it 'unique' based on an ordinal position (relative to the dimension hierarchy) to try to sort on this structure. Where there was a 'collision' i.e. 'Claims  ' (3 spaces) had been previously loaded this wouldnt matter as it still represented the 3rd ordinal position in the list (relative to the hierarchy). This is a sample of the code:

05-11-2014 15-50-44.png

where e.g.:

'lL2' represents the selections for a list box (the pivot table was made 'read only')

'Level2' the 'Item' with a number of spaces appended to make it 'unique' in the list relative to the hierarchy (Level1,Level2,Level3 etc)

anL2 the ordinal position in the list relative to the hierarchy (on which Level2 would in theory be sorted in the pivot table)

I get the feeling it should have been easier but I tried everything and just glad to get past the issue!

Regards,

Gordon