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

Sorting by Column dynamically in Pivot Table

Dear experts,

I have a pivot table to show country performance over years, and I need to let the user to select the sorting order by year on demand.

I tried setting the Sorting by Sum(Sales) by that will sum up all sales from all years selected, and I cannot sort by 2012 in specific

Country20102011201220132014
China100110130160180
Singapore70905080100
Malaysia40608010090
Taiwan5060708090
Japan3080403050
Korea2040603080

My data in the database is stored in the format

CountryYearSalesMargin
China201010060
China201111070
Taiwan20149040
............

How can I pick any year (say 2012) and sort the sales by that year?

Moreover, can I do a sorting by 2014 first then 2013?

What if I have 2 expressions (both Sales and Margin) under each year, and I want to further enhance the table to sort any of those columns?

Country20102011201220132014
SalesMarginSalesMarginSalesMarginSalesMarginSalesMargin
China1006011070130100160120180130
Singapore704090505030803010060
Malaysia402060308050100409040
Taiwan50206010702080309040
Japan30108030402030105020
Korea20104020603030208050

Last but not least, I would still the option to sort by the Country name.

Thanks a million!

Regards,

Alfred

6 Replies
deepakqlikview_123
Specialist
Specialist

Hi Alfered,

In pivot table you can not sort whatever u want.

You have to sort as first dim then second dim till nth.

Thanks,

ecolomer
Master II
Master II

If you mean interactive sort (sorting by user on click on column header), that is not allowed in pivot table.

For interactive sort you change to straight table

alfredlee
Contributor III
Contributor III
Author

In fact I do not need a clean and neat, and I accept workaround. I guess some advanced formula with set analysis may help, but I do not know how that could be applied. The problem I have is that we had a old BI tool just allow us to do sorting by column on demand, and we think QV is a better tool and should be able to that as well.

Thanks for your replies!

alfredlee
Contributor III
Contributor III
Author

Year" is a dimension, and a Straight table might not work in my case

mvanlutterveld
Partner - Creator II
Partner - Creator II

Hi,

In attached app I created an interactive sorted Pivot table based on the sales of a selectable year. Is this what you need?

Regards,

Michiel

Stefan_dT
Contributor III
Contributor III

In the below 2 video's Christof Schwarz shows some workarounds in order to get a pivot table to sort any way you want it.  I think the 1st might apply more to your requirement, in that you enable the customer to sort by any column (ascending or descending) by a simple click.

 •  https://www.youtube.com/watch?v=iiMlRVJWmsk
 •  https://www.youtube.com/watch?v=PfPz_JOH1XE


He does show it in Qlik Sense - no idea if you can mimic this in QlikView