Sorting in Qlikview Pivot table with common Column Values

[timanshu]

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:

[jonathandienst]

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.

[yashdeep]

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

[timanshu]

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.

[timanshu]

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.

[yashdeep]

can you please post sample qvw

[timanshu]

Hello All,

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

Pivot table showing incorrect sorting .

[timanshu]

attached. Please check my reply to original post.

[yashdeep]

Try the solution in the attached qvw.

its not perfect but hope it works for you.

[timanshu]

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.