3 Replies Latest reply on Aug 29, 2016 7:52 AM by Paraskev Bentchev

    Sorting and ranking

    Ajit Kumar

      Hi,

       

      I have attached a workbook, where I have brought market and Product type with Profit.

       

      I have to give Ranking to Product Type on the basis of Profit. So the highest profit product type should have rank 1 and the lowest should have last rank for the respective market.

       

      I am trying to do that by using Index().

       

      I have sorted the Product type in Descending order on the basis of profit but sorting is also giving the wrong number. I thought firts I will sort the Product type in Descending order on the basis of profit and then I will bring Index () and by using the table calculation with Product Type I will get the Ranking. But nothing is working fine.

       

      I am not able to do that, please help me on this.

        • 1. Re: Sorting and ranking
          Alex Kerin

          You were getting there - first we need to sort the index by the sum of the profit - this explicitly sets the order and won't change if the view changes. Then because you need a nested sort we also have to move both dimensions to the right and restart the partition every market change. This is kind of similar to doing a nested sort using a set.

           

          table.png

          1 of 1 people found this helpful
          • 2. Re: Sorting and ranking
            Ajit Kumar

            Hi Alex

            Thanks for the solution. I applied the same in my workbook which I have attached in the first draft (File - Value Driver)

            Where I am getting two Brands for a single Rank.

             

            Would you please help me on that

            • 3. Re: Sorting and ranking
              Paraskev Bentchev

              Hi,

               

              As I understand the problem is caused by the Month, It calulates StockedList per month and gives the Rank or Index.

              On the second sheet the month is on columns shelve and you are calculating index like using Table Down. But when you add level of details on month of the date it reclaculates the Rank/Index.

              So I suggest to use LOD to calc the stockedlist without date

              Create Calculated field:

              {EXCLUDE  [DateKey] : SUM([StockedList])}

              and sort brand on it

               

              Hope this will help