Sorting by Column dynamically in Pivot Table

[alfredlee]

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

Country	2010	2011	2012	2013	2014
China	100	110	130	160	180
Singapore	70	90	50	80	100
Malaysia	40	60	80	100	90
Taiwan	50	60	70	80	90
Japan	30	80	40	30	50
Korea	20	40	60	30	80

My data in the database is stored in the format

Country	Year	Sales	Margin
China	2010	100	60
China	2011	110	70
Taiwan	2014	90	40
...	...	...	...

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?

Country	2010		2011		2012		2013		2014
	Sales	Margin	Sales	Margin	Sales	Margin	Sales	Margin	Sales	Margin
China	100	60	110	70	130	100	160	120	180	130
Singapore	70	40	90	50	50	30	80	30	100	60
Malaysia	40	20	60	30	80	50	100	40	90	40
Taiwan	50	20	60	10	70	20	80	30	90	40
Japan	30	10	80	30	40	20	30	10	50	20
Korea	20	10	40	20	60	30	30	20	80	50

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

Thanks a million!

Regards,

Alfred

[deepakqlikview_123]

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]

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]

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]

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

[mvanlutterveld]

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]

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