topic Pivot table sorting not working in QlikView

Pivot table sorting not working

ankitaag — Fri, 22 May 2015 07:50:56 GMT

Hello All,

I am attaching a sample application where I want the user to select any 1 measure from the listbox and the pivot table should get sorted accordingly.

Please help!

morganaaron‌

Thanks and Regards,

Ankita

Re: Pivot table sorting not working

Gysbert_Wassenaar — Fri, 22 May 2015 09:25:30 GMT

Looks to me like it works. It first sorts on Region, then sorts the Departments within the region and so on.

Re: Pivot table sorting not working

sunny_talwar — Fri, 22 May 2015 09:27:43 GMT

Similar idea. Check out this link: Re: Sorting not working

HTH

Best,

Sunny

Re: Pivot table sorting not working

ankitaag — Fri, 22 May 2015 10:06:26 GMT

sunindia,

thats the link from my own team mate.. so its not useful for me..Thanks

Re: Pivot table sorting not working

sunny_talwar — Fri, 22 May 2015 10:09:13 GMT

Hahahaha ya I figured that, but I guess the answer still stays the same that the kind of sorting you are looking for might not work with a pivot table. But I will still not pass any judgement and will wait for somebody to may be give you a solution you are looking for

Best,

Sunny

Re: Pivot table sorting not working

ankitaag — Fri, 22 May 2015 10:09:26 GMT

Hi,

Yes I totally agree with that but for Canada Region, Department='', Role='Others' should come first as it has the highest value.

Re: Pivot table sorting not working

ankitaag — Fri, 22 May 2015 10:10:37 GMT

Hi Sunindia,

Thankyou.

But previously it was working fine and now I have no idea why it is not working.

Re: Pivot table sorting not working

sunny_talwar — Fri, 22 May 2015 10:13:19 GMT

Oh okay.

I would love it if you can share the solution once you have it pinned.

Thank you.

Re: Pivot table sorting not working

ankitaag — Fri, 22 May 2015 14:00:01 GMT

FYI

utkarshgarg‌

Re: Pivot table sorting not working

swuehl — Fri, 22 May 2015 14:16:52 GMT

You sort Role by sum(Amount), and Role '' has the highest value 1033 + 543 + 1222, so it comes first.

Sum(Amount) will not be considering the preceding dimensions.

(Think about how it will affect sorting when pivoting Role to the top otherwise)

Re: Pivot table sorting not working

timanshu — Wed, 27 May 2015 13:12:20 GMT

Hi Ankita,

Swuehl is right.

when sorting role, it sums up amount of each role independent of Region and department.

For sorting to work, do this:

Replace Department sorting expression to :

Pick(

match(Sort,'Quantity','Amount'),

aggr(sum(Quantity) ,Region),

aggr(sum(Amount) ,Region)

)

and Role sorting expression to :

Pick(

match(Sort,'Quantity','Amount'),

aggr(sum(Quantity) ,Region,Department),

aggr(sum(Amount) ,Region,Department)

)

similarly add Role for user sorting expression

Re: Pivot table sorting not working

ankitaag — Wed, 27 May 2015 13:26:24 GMT

Thankyou for your reply Timanshu,

Though it is still not working. Lol

Re: Pivot table sorting not working

ankitaag — Wed, 27 May 2015 13:27:43 GMT

Hi, Please find attached the app as per your solution.

Re: Pivot table sorting not working

timanshu — Wed, 27 May 2015 18:29:04 GMT

Hi,

So now where is sorting wrong. I found the report correctly sorted in attached app.

Re: Pivot table sorting not working

swuehl — Wed, 27 May 2015 21:54:14 GMT

Not sure what Ankita wants to demonstrate with this sample.

It's different from the original by the way dimension values are related to higher dimensions. For example, each user is related to only one single role. But the sorting issue will only be visible if you have a 1:n relation, e.g. a user is related to more than one role. In Ankita's original post, the empty or blank role shows this issue because it's related to many departments.

Timanshu, I don't think your solution will work. Try with a simple sample I've attached (try to sort Field2).

Re: Pivot table sorting not working

timanshu — Thu, 28 May 2015 06:18:03 GMT

Hi Swuehl,

Yes It does not work for the case you provided. I think , Pivot table sorts like a straight table and it does not consider grouping while sorting so that values under different groups sort in different way.

So , any way that could make sorting work in this case?