Aggr / Rank / top?

[yipchunyu]

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.

[Anil_Babu_Samineni]

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

[yipchunyu]

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. 
*******

[sunny_talwar]

---

@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

Rank Function

---

@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

Pitfalls of the Aggr function

[yipchunyu]

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)