Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a chart (pivot table) which shows the detail information of a transaction (the trx no, sale amount, product category, product group, product name ...)
I want to add a new field to calculate the ranking of the total sale amount grouped by category and product group.
Search the forum and found many experts mentions the use of aggr and rank but have no ideas on it.
Any advice is welcomed.
Perhaps this?
Rank(Sum(Sales)) //This is enough for you, I believe
Or
Sum(Aggr(Rank(Sum(Sales)), Category, [Product Group])) //This is only for cells of rows including Sub Total
Or
Aggr(Rank(Sum(Sales)), Category, [Product Group]) //This is only for cells of rows
Thanks a lot.
Would you pls help to elaborate a little bit more of the 2 and 3 options?
For some case, the option 2 will return some numbers e.g. 1.5 (which I think it's odd for ranking).
Ranking | Sum(Aggr(Rank(Sum(Sales)), Category, [Product Group])) | Aggr(Rank(Sum(Sales)), Category, [Product Group]) |
1-2 | 1.5 | 1-2 |
1-2 | 1.5 | 1-2 |
*******
And as if i create a new expression like below:
aggr(Sum(Sales), Category, [Product Group]), the calculated total only present in a few rows. What's the logic?
Found the keyword Nodistinct should be used in this case, just record here for other reference.
*******
@yipchunyu wrote:Thanks a lot.
Would you pls help to elaborate a little bit more of the 2 and 3 options?
For some case, the option 2 will return some numbers e.g. 1.5 (which I think it's odd for ranking).
Ranking Sum(Aggr(Rank(Sum(Sales)), Category, [Product Group])) Aggr(Rank(Sum(Sales)), Category, [Product Group]) 1-2 1.5 1-2 1-2 1.5 1-2
If two rows have the same Sum(Sales), the Rank divides the ranking on the two rows. There is a parameter within the Rank() function which you can use to get different values of rank when you run into those situations. I will look here to understand Rank() function here
@yipchunyu wrote:
And as if i create a new expression like below:
aggr(Sum(Sales), Category, [Product Group]), the calculated total only present in a few rows. What's the logic?
Found the keyword Nodistinct should be used in this case, just record here for other reference.
If you more than 2 dimension in your chart and you are using the above expression, you can run into an issue which is called grain mismatch. You already found a work around (NODISTINCT), but read more on this here
Thanks a lot for all the advice and knowledge sharing.
I tried to apply the rank in other cases but got issues again (so sorry).
COMPANY_NAME | QUARTER | TYPE | YTQ_USD |
A | 2018Q2 | Total | 9,832 |
B | 2018Q2 | Total | 1,158,893 |
C | 2018Q2 | Total | 0 |
D | 2018Q2 | Total | 0 |
E | 2018Q2 | Total | 0 |
F | 2018Q2 | Total | 198,191 |
G | 2018Q2 | Total | 98,876 |
I will select the Quarter and type (including, total, channel1, channel2 ...) and then the column YTQ_USD will calculate the amount for that company by quarter by type.
I simply want to add a ranking by (YTQ_USD) by it doesn't work. (which either 1 or 0 being shown).
Anything goes wrong?
##update
Just trial and error and found below expression seems to work but can't fully understand the logic.
Aggr(NODISTINCT Rank(Sum({$} #YTQ_USD)), COMPANY_NAME)