5 Replies Latest reply: Sep 1, 2014 6:58 AM by Michiel van Lutterveld RSS

    Sorting by Column dynamically in Pivot Table

    Alfred Lee

      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