Ranking Braches

[#1]

Hi Experts

I am having a Sales table with multiple branches,Customers and their sales

now I want to rank the customers for each Month by branch according to sales  and take top 5 customers

from each branch to a chart

I want the ranking to done on the Script itself

please help me in doing this

[sunny_talwar]

Rank the total sales (for all dates) or each date's rank?

[#3]

HI Sunny

Sorry Rank by Month

[sunny_talwar]

Try the following script:

Table:

LOAD branch,

    date,

    MonthName(date) as MonthYear,

    customer,

    sales

FROM

Community_169202.xlsx

(ooxml, embedded labels, table is Sheet1);

Join(Table)

LOAD branch,

  customer,

  MonthYear,

  Sum(sales) as TotalSales

Resident Table

Group By branch, customer, MonthYear;

Join(Table)

LOAD distinct branch,

  MonthYear,

  customer,

  TotalSales,

  If(branch = Peek('branch'), Alt(Peek('Rank'), 1) + 1, 1) as Rank

Resident Table

Order By branch, MonthYear, TotalSales desc;

Best,

Sunny

[#5]

Hi Sunny

When I use this script in my actual data, My System Got hanged twice

Is there any other option for this

please Suggest

[sunny_talwar]

How big is your database?

[#7]

Hi Sunny

22 million transaction Lines

there are 152 branches

[swuehl · Accepted solution]

As I've understood, you wan to rank sales by customer per MonthYear and branch.

I think above solution only does it per branch.

Joining the complete fact table back to itself is somewhat ressource expensive (and the distinct load prefix will be performed on the output table!).

Maybe try something like this:

// First create more random data, since your excel only shows data for a #1 customer rank per MonthYear and branch.
// You don't need the foor loop and the customer / sales mods, just load your real data from your source.

FOR i = 1 to 20

Table:
LOAD branch,
    date,
    MonthName(date) as MonthYear,
    customer + floor(RAND()*10) as customer,
    sales + ceil(RAND()*100) as sales
FROM
Community_169202.xlsx
(ooxml, embedded labels, table is Sheet1);

NEXT i

// sum(sales) grouped by customer, branch, MonthYear
TMP:
LOAD branch,
  customer,
  MonthYear,
  Sum(sales) as TotalSales
  //Rank(Sum(sales)) as Rank2
Resident Table
Group By branch, customer, MonthYear;

//Create the rank and Join back to fact table. You can comment out the preceding load to get a full ranking

LEFT JOIN (Table)
LOAD * WHERE Rank <= 5;
LOAD
  branch,
  customer,
  MonthYear,
  TotalSales,
  AutoNumber(customer, branch & MonthYear) as Rank
RESIDENT TMP
order by branch, MonthYear, TotalSales desc;


drop table TMP;

[Digvijay_Singh]

Dear Swuehl,

I am learning a lot from your responses(Thanks!).

Just couldn't get what portion is giving rank 1 to highest sale group by monthyear and then rest of the ranks in descending order of TotalSales?

Thanks,

Digvijay

[swuehl]

In the last load statement, the records will come in ordered by branch, MonthYear, TotalSales descending, so you just need to start counting customers from 1 whenever there is change in the combination branch and MonthYear.

This is done by Autonumber() function, you can do the same with a peek() logic as sunindia demonstrates above.

edit: For me, it's a little easier to read and understand what the autonumber is doing compared to the method using peek().

But I've seen perfomance issues using Autonumber() when there is a large number of combinations of branch / MonthYear.