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:

       

      Correct

      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:

       

      Incorrect

      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!

      Regards,

      Andrew

        • 1. Re: sorting set
          Shinichiro Murakami

          Andrew,

           

          Yes, Table calc is always headache to me.

          Could you check attached workbook meets your request or not.

           

           

          [index]

          index()

           

          [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)

          end

           

          [Sub Category_Display]

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

           

           

           

           

          Behavior

           

          Sort on 2013

           

          Sort on 2014

           

           

          Thanks,

          Shin

          • 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:

             

            correct

            incorrect

             

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

            Thank you for your time!

             

            Regards,

            Andrew

            • 3. Re: sorting set
              Shinichiro Murakami

              Andrew,

               

              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....

               

               

              Thanks,

              Shin