2 Replies Latest reply on Nov 27, 2016 3:20 PM by Nachiket Dekhane

    Rank Sub-Categories based quantities over 4 weeks

    Nachiket Dekhane

      Hi Folks,

       

      I am stuck with a table calculation problem. Here are my requirements for this exercise.

       

      1. Sub-categories should be ranked based on quantities over 4 weeks

      2. Top 2 sub-categories should be shown as they are.

      3. Rest of the sub-categories should be grouped as Other with their quantities rolled up to the group level

       

      I tried using rank function and was able to do implement for one week. But I am not able to replicate the same when there are more weeks involved as some of the sub-categories don't have any data for some of the weeks. I also tried using index but not able to sort based on sum of quantities over multiple weeks.

       

       

      I am attaching sample workbook which refers to Superstore data. Any help in this regard is highly appreciated.

       

      Thanks,

      Nachiket