5 Replies Latest reply on Jun 28, 2017 10:31 AM by Hari Ankem

    Top N parameter issue

    kamalakshi parshuram

      Hi Friends,

       

      Need help on this below,

       

      Here i am trieg to filter "Top N Annualized spend",  for reference keeping INDEX() as SL No, but i am unable to get exact Number which i enter in Top N parameter. Also mention where i am going wrong.

       

      Also One more parameter "Top N Spend" i want to add, for same sheet. So final result which i want is - i have to use both the parameters for same sheet.

       

      Waiting for positive reply.

       

      Find the attached workbook.

       

      Thanks & Regards
      Kamalakshi

        • 1. Re: Top N parameter issue
          Hari Ankem

          You need to set the context whenever you need Top N data. You have to set it for all the dimensions except the one in which you apply the Top N filter. This will ensure that you get the right output within the filtered data (context).

           

          1. To set the context, right click on the dimension in the filter and add to context. You have to add Project Status, Savings Lever and Savings Type to context.

          Capture.PNG

           

          2. After adding the dimensions to the context, you may still feel that you are not getting Top N as required.

          In the the below data, it seems as if the total records shown are 16 based on the serial number. But, in reality the output is correct because you have 10 distinct projects. Since you have the SBG dimension, the Project is broken out further to increase the number of records.

           

          Capture.PNG

           

          3. You should set the table calculation for the Serial Number so that the count is displayed correctly as shown below. Right click on the Sl No table calculation and click on "Edit Table Calculation".

           

          Capture.PNG

           

          The updated workbook is attached.

          • 2. Re: Top N parameter issue
            kamalakshi parshuram

            Hi Hari Ankem,

             

            Thank You So Much, its working exactly.

             

            I have one more Question,

             

            Is it possible to use one more parameter for the same sheet..? Which is already there in workbook "Top N Spend", it has to work same as "Top N Annualized spend" but it should filter based on "Spend" instead of Annualized Spend.

             

            Thanks & Regards

            Kamalakshi

            • 3. Re: Top N parameter issue
              Hari Ankem

              To be able to get the top N for Spend or Annualized spend, you should create a parameter that lets you identify which metric you need. Then use that parameter to create a calculated field that gets you the selected metric value.

               

              1. Parameter creation:

              Capture.PNG

               

              2. Calculated Field to assign the selected metric value based on the parameter selection.

              Capture.PNG

               

              3. Use the above calculated field to identify which Top N you need.

              Capture.PNG

               

              Here are your results now:

              1. Top 15 by Annualized Spend

              Capture.PNG

              2. Top 15 by Spend

              Capture.PNG

               

              You can change your N value as required. Hope this helps.

              • 4. Re: Top N parameter issue
                kamalakshi parshuram

                Hi Hari Ankem,

                 

                Thank You So Much for your support. It's working. So happy to see the solution with the step by step.

                 

                Regards

                Kamalakshi

                • 5. Re: Top N parameter issue
                  Hari Ankem

                  You are welcome.