3 Replies Latest reply on Dec 14, 2016 10:18 PM by Shinichiro Murakami

    sorting set

    Mikey Michaels

      Hello All,

      I hope this message finds you well.

      I've posted a similar question to the forum but have not received the desired solution as I think I did not explain my question well.

      On the attached, I have two charts - one is labeled "Non Set" and the other "Set".


      Here is what I'm after:

      I would like to be able to dynamically sort the "Set" chart by each individual year. So when a user wants to see the top  7 subcategories within the set for "2014", they should see the following:



      My chart labeled "Set" isn't sorting properly, as I believe it is showing the the top 7 subcategories across all years, not just 2014. See the chart below:



      As you can see, Copiers are not listed in the chart above and it should be. And machines should not be listed.


      Is there a way to have the "Set" chart dynamically sort so when an individual year is selected the set will update based on the selection?


      Thanks so much for your time!



        • 1. Re: sorting set
          Shinichiro Murakami



          Yes, Table calc is always headache to me.

          Could you check attached workbook meets your request or not.






          [index filter]

          if index() <= [Top N Sub-Category] or  last()=0 then "show" else "hide" end


          [Set In Out]

          if [Index] <= [Top N Sub-Category] then "IN" else "OUT" end


          [Sum Sales]

          if index() <=[Top N Sub-Category] then sum([Sales])

          ELSE window_sum(if index()> [Top N Sub-Category] then sum([Sales]) end)



          [Sub Category_Display]

          if [Index] <= [Top N Sub-Category] then attr([Sub-Category]) else "Other" end







          Sort on 2013


          Sort on 2014





          • 2. Re: sorting set
            Mikey Michaels

            Hi Shin,

            Thank you for taking a look at this. I think we are close, but we're not quite there.

            When I opened your attachment, I tried to sort on order date = "2013" and I would expect the following results:





            As you can see, "machines" should be in the view and 'accessories' should not.

            Thank you for your time!




            • 3. Re: sorting set
              Shinichiro Murakami



              Looks like we need to use parameter to determine "sort year".


              [Sort Year]

              sum(if year([Order Date (Years)])=year([Order Date (Years) Parameter])

              then [Sales] end)


              Initially I thought it worked.  I did sort by year and then add index, it showed correctly, but when I change the sort year, It brings back to original rule of sorting by sales by entire time horizon.


              Now I needed to specify which year's data I want to use as sort value to change the display sub-category dynamically....