2 Replies Latest reply on Aug 22, 2017 8:57 AM by Norbert Maijoor

    TOP N and All option

    Kiran K

      Hello Team,

       

      I have requirement to show TOP N customers and ALL option to select from parameter, here is my requirement.

      Options are :

      • TOP 10 by Volume on Customer
      • TOP 10 by Net Revenue on Customer
      • TOP 10 by Profit on Customer and
      • ALL (non filtered data)

       

      Can you please help me find out the solution. Thanks!

        • 1. Re: TOP N and All option
          Joshua Milligan

          Kiran,

           

          This is a great question!


          Here's the approach I would take:

           

           

          First, I'd create a parameter with the options.

           

          Then, I'd create a Set for each option (except All):

           

          Just be aware that Sets are computed within Context, meaning you'll get the top 10 customers within the entire data set by default (and they could be removed by filters).  If you want to find the top 10 within the filtered data, add the filters to context.

           

          Then, with the 3 different sets, create a calculated field that will return True for anything you want to keep (the Set name will return True or False based on whether the customer is in the set or not)

           

          IF [Filter Option] = 1 THEN [Top 10 Customers by Volume]

          ELSEIF [Filter Option] =  2 THEN [Top 10 Customers by Revenue]

          ELSEIF [Filter Option] = 3 THEN [Top 10 Customers by Profit]

          ELSE TRUE

          END

           

          Place that calculated field on filters and keep the True values and you should now have a filter controlled by the parameter

           

          Hope that helps!

          Joshua

          • 2. Re: TOP N and All option
            Norbert Maijoor

            Hi Kiran,

             

            Find my approach based in sheet-selection as reference below and stored in attached workbook version 10.2 located in the original thread.

             

            1. sheet selection is explained here

             

            Regards,

            Norbert