6 Replies Latest reply: Oct 24, 2017 11:12 AM by Frédéric Villemin

Adding rank based on 1 dimension in the script

Hello,

I have a table of sales with two dimensions called Country and Customer and a KPI called Amount.

I would like to add in this table, for each line, the rank of the customer based on the total amount of this Customer in this Country

For example :

Germany, Apple, 1

Germany, Microsoft, 100

Belgium, Microsoft, 50

would become :

Germany, Apple, 1, rank 3

Germany, Microsoft, 100, rank 1

Belgium, Microsoft, 50, rank 1

How can I do that in scripting ?

Thanks

• Re: Adding rank based on 1 dimension in the script

May be this

Table:

Country, Company, Sales

Germany, Apple, 1

Germany, Microsoft, 100

Belgium, Microsoft, 50

];

FinalTable:

If(Country = Previous(Country), If(Company = Previous(Company), Peek('Rank'), RangeSum(Peek('Rank'), 1)), 1) as Rank

Resident Table

Order By Country, Company, Sales desc;

DROP Table Table;

• Re: Adding rank based on 1 dimension in the script

It was almost perfect

I just had to change the ORDER BY to :

Order By Country,  Sales desc;

in order to have the sales sorted by total (biggest first) and with the rank depending on the Country

• Re: Adding rank based on 1 dimension in the script

But then if you sort by Sales, then the same company can be at different places and you may get different rank for them?

• Re: Adding rank based on 1 dimension in the script

Oh yes you are right .. that's because I have made it on my own project where only one line exists for each Country/Company

But if I take your example Apple is first in Germany where it should be 3rd because of only 1 sale.

What should I do to make your example work ?

• Re: Adding rank based on 1 dimension in the script

May be based on total sales

Table:

Country, Company, Sales

Germany, Apple, 1

Germany, Microsoft, 100

Belgium, Microsoft, 50

];

Left Join (Table)

Company,

Sum(Sales) as TotalSales

Resident Table

Group By Country, Company;

FinalTable:

If(Country = Previous(Country), If(Company = Previous(Company), Peek('Rank'), RangeSum(Peek('Rank'), 1)), 1) as Rank

Resident Table

Order By Country, TotalSales desc, Country;

DROP Table Table;

• Re: Adding rank based on 1 dimension in the script

absolutely excellent !

Thanks