topic Re: Sorting in Qlikview Pivot table with common Column Values in QlikView

Sorting in Qlikview Pivot table with common Column Values

timanshu — Wed, 13 Jun 2018 09:52:28 GMT

I have one excel with columns values sorted as shown in screenshot 1. Required Sorting is mentioned in column Bs.Sno

I want to show this in pivot table (QlikView) with same sorting.

I tried to do so as shown in screenshot 2 but sorting is not correct.

For example Sno 13 is not appearing at correct position. It is appearing after Sno. 17.

This is happening because I have same values in columns lying under different groups.
(ie Financial assets of column 3 is under Non current assets as well as under Current assets of Column 2)

I don't want to change column Values.

I have attached the raw excel same as shown in Screenshot 1.

How to tackle this ???.
Please Suggest.

Screenshot 1 :

Screenshot 2:

Re: Sorting in Qlikview Pivot table with common Column Values

jonathandienst — Wed, 13 Jun 2018 10:14:50 GMT

A pivot table (in Excel as well as in Qlik) sorts from left to right. If you want BS.Column3 as a dimension, then it will be sorted before Sno, so Sno 17 is reported before Sno13 as they are in different categories. If you need to sort by Sno, perhaps you need a straight table rather than a pivot.

Re: Sorting in Qlikview Pivot table with common Column Values

yashdeep — Wed, 13 Jun 2018 10:49:39 GMT

In Chart properties go to Sort tab and uncheck sort by criteria for all dimensions and for dimension BS.Column4

in Sort By>>Expression add Bs.Sno and set it as ascending

Re: Sorting in Qlikview Pivot table with common Column Values

timanshu — Wed, 13 Jun 2018 11:01:32 GMT

Hi Jonathan,

Thank you for your reply.

Sno 13 is Inventories in Column 3. This inventories can come above Financial Assets.It is possible since they are differently grouped.
I have applied sorting on expression on all columns with expression = Sno (Acceding). I dont want to Show Sno. you can put any expression instead of that. It is just for reference.

As pivot table is sorted left to right. Column 1, Column 2 are correct. from column 3, Inventories is 13 and it should come before Financial assets in which every row is more than 13.

Therefore requirement is logical. I want this in pivot as per requirement.
Please suggest.

Re: Sorting in Qlikview Pivot table with common Column Values

timanshu — Wed, 13 Jun 2018 11:03:22 GMT

Hi Yashdeep,

Thank you for your reply.

I did the same thing also. It didn't worked. Sorting was similar to whhat I attached in screenshot 2. You can check and verify.

Re: Sorting in Qlikview Pivot table with common Column Values

yashdeep — Wed, 13 Jun 2018 11:47:11 GMT

can you please post sample qvw

Re: Sorting in Qlikview Pivot table with common Column Values

timanshu — Wed, 13 Jun 2018 12:16:14 GMT

Hello All,

Please Find the attached qvw with created straight table and pivot table.

Pivot table showing incorrect sorting .

Re: Sorting in Qlikview Pivot table with common Column Values

timanshu — Wed, 13 Jun 2018 12:16:45 GMT

attached. Please check my reply to original post.

Re: Sorting in Qlikview Pivot table with common Column Values

yashdeep — Thu, 14 Jun 2018 12:15:47 GMT

Try the solution in the attached qvw.

its not perfect but hope it works for you.

Re: Sorting in Qlikview Pivot table with common Column Values

timanshu — Tue, 19 Jun 2018 09:35:26 GMT

Thank You Yashdeep for your efforts.

This will not work in current requirement as requirement is to see the values clubbed as well as in expanded form. That will happen if I would be able to collapse/expand the group. Restricting some of pivot values to be ungrouped. like in Financial assets of column 3 will not help.