Rank using Unique Item


I have a module which shows change in volumes by Customer - Product (over 5000 lines)

I then rank these changes largest to smallest, and have a view of that.

Customer ProductLast MonthCurrent MonthChangeRank
Customer A - Product 1340,480311,576-28,9051
Customer A - Product 270,32646,326-24,0002
Customer D - Product 1403,775394,675-9,1003
Customer C - Product 323,38716,393-6,9944
Customer A - Product 417,02910,226-6,8035
Customer A - Product 572,60066,631-5,9696
Customer A - Product 617,00012,525-4,4757
Customer D - Product 210,5726,180-4,3928
Customer A - Product 7112,126108,801-3,3259
Customer B - Product 232,44629,364-3,08310


Now what I need to do is extract the top 3 customers from this, without duplicates
i.e Customer A has the 2 biggest changes but the customers I want are A and D and C

Cust - RankCustomer
1Customer A
2Customer D
3Customer C
4Customer B


I have tried many ideas but can't seem to solve this one.



Best Answer

  • Misbah
    Answer ✓


    See if this helps!


    Step 1: Create Customer List if it is not already available.

    Step 2: Use the following calculations ( Rank Line will be calculated once the second module is created)



    Step 3: Create the module where you will be checking for Max values against customers



    Step 4: Reference Rank in the first module from second module.


    Hope that helps


    Miz Logix