Ranking in data.table with multiple criteria

table: Brands_12M as data.table
Names(Brands_12M): "Major Category", "Brand", "Sales"

There are 5 major categories and 500 brands.

I am trying to add "Rank" column to based on "Sales" by "Major Category" using:

Brands_12M <- Brands_12M[,Rank := frankv(Brands_12M, "Sales", "Major Category", order=-1L)]

I am getting rank 1-500 for all brands ignoring "Major Category". I need rank by brand for each "Major Category" i.e. 1-100, 1-100 etc

Greatly appreciate any suggestion.

Solution

I changed my answer after looking at all the comments. This seems to be closer to what is suggested. The sample data set I use below has 500 observations and that seem to be what the poster wants.

Also the ranks go from 1 to 100 inside each Major Category.

library(data.table)

Brands_12M <- data.table(`Major Category` = rep(1:5,100),
                         Brand = 1:500,
                         Sales = runif(500))

Brands_12M <- Brands_12M[,Rank:=frankv(Sales, order=-1L),by=c("Major Category")][order(`Major Category`,Sales)]