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: 
timanshu
Creator III
Creator III

Sorting in Qlikview Pivot table with common Column Values

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 :

T1.PNG

Screenshot 2:

T2.PNG

9 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
yashdeep
Contributor III
Contributor III

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Contributor III
Contributor III

can you please post sample qvw

timanshu
Creator III
Creator III
Author

Hello All,

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

Pivot table showing incorrect sorting .

timanshu
Creator III
Creator III
Author

attached. Please check my reply to original post.

yashdeep
Contributor III
Contributor III

Try the solution in the attached qvw.

its not perfect but hope it works for you.

timanshu
Creator III
Creator III
Author

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.