2 Replies Latest reply on Nov 20, 2017 8:35 AM by nick liu

    Top N filters based on a dimension

    nick liu

      Sorry if this is a really simple question:


      I have the following excel file:


      e.g: Column A represents the part number with the volume for each date on Column B and C.


      Column F represents the ranking for each part number by volume and by date. Therefore, part c (row 4) is ranked as "1" for Oct 23rd as its volume is the largest for Oct 23rd (=10)

      and part b is ranked as "2" for Oct 30th.



      I want to put a filter on Part number to show the Top N parts (e.g: a filter has 5 options, being 1, 2, 3, 4, all parts, as selecting 2 would only show Top 2 parts for each date). I googled about Top N filters and tried to put Part number under filter and set the at most stuff (e.g: shown in the following screenshot),





      it is working fine, But I just wonder if I could create like a parameter to change the format/layout of the filter into something like this:

      (e.g: it would be extremely nice to have an option at the very bottom to "select" all parts)


      I know how to create a parameter, but just don't know how to transfer my existing ranking column F into a parameter-wise filter.

      top n parats.PNG




      Anyone could help me on it? Thanks a lot!!!


      I attached the workbook.

        • 1. Re: Top N filters based on a dimension
          Joe Oppelt

          IF [Parameter Name] = "All" or [F] <= INT([Parameter Name]) then 1 else 0 END


          Put that on your filter shelf and select for value = 1.


          Make the parameter a text field.  Have the specific numbers you want as choices, and have "All" as your last choice.  (You could also make it an integer parameter, and have display values that are string, but for the "All" display value the internal integer value could be something like a million.  And then in that case you wouldn't need the first half of the IF syntax I gave.  You'd just need the <= comparison.)

          • 2. Re: Top N filters based on a dimension
            nick liu

            Hey Joe!


            Thanks!! It works like a charm!!!!!!!!