Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

Re: Pivot table sort

In sorting expression use simply HashVal (not min of it) and ... let me know

Not applicable

Re: Re: Pivot table sort

Hi Alessandro,

Thanks for the reply.

Just 'HashVal' in the expression doesn't work either.

I've attached an extract from the straight table if you want to see the problem for yourself (my way of saying I'd appreciate the help!)

regards,

Gordon

Re: Re: Pivot table sort

An idea, just try:

use hashval as expression (it must be the first expr.) then order by Y value

Not applicable

Re: Pivot table sort

Gordon Alessandro answer is the right way to do it but you have to put hasval in expression sort for all the dimensions not just for one

Re: Pivot table sort

In a pivot table sorting a sub dimension [i.e.HashVal], in preference to the upper dimensions does not make logical sense.

Use a straight table and it should work.

Or in your pivot table promote the HashVal dimension to the top.

Not applicable

Re: Re: Pivot table sort

Hi All,

Thanks for the replies.

I tried adding the expression to all the dimensions and then checking 'Y value' but it seems to mess it up even more:


24-10-2014 17-02-00.png

Bill - you can't prmote columns in sort for pivot tables.

Regards,

Gordon

Re: Pivot table sort

Promote the dimension on the dimension tab.

Not applicable

Re: Re: Pivot table sort

Hi Bill,

If I do that it then pivots on HashVal:

24-10-2014 17-14-06.png

This needs to be a pivot table and I dont want to display HashVal anyway. HashVal simply defines the logical sequence of the data.

This should be easy!

Regards,

Gordon

Not applicable

Re: Re: Re: Pivot table sort

Hi all,

I still can't get this to work unfortunately and would really appreciate any help.

Perhaps an explanation is required. I'm trying to pull together some security reports that identify permissions by user group for items on menus. I want to mimic the sequence to make it easy to cross reference. Some menus/items won't appear in the security report for all user groups e.g. no access for a group of items means the lower level items won't appear; I resolved this (I believe) by creating an autonumber based on the concatenation of all the levels.

This is an example of a menu for a module:

menu.png

The items for 'Places' and 'Common Forms' are not grouped as such, whereas those items e.g. Journals/Reports etc do.

Attached is a qvw which contains the transformed security report for a single user group and hopefully demonstrates the sorting issue.

If anyone has the time, I'd sure appreciate it.

Reagrds,

Gordon

Community Browser