Create a Rank as a Dimension in Pivot Table

[mrthomasshelby]

Hello,

I have a pivot table as follows:

Now what I want to add this table is a dimension called 'Rank' which ranks the suppliers on the basis of the field 'Savings,K'.  Savings, K is calculated as :

Sum(([Price1] - ([Quote Price]*(1-[Rebate])))*(Volume))/1000

In other words I want to make my table look like below:

	Rank 1			Rank 2			Rank 3	Rank 4
Package Name	Supplier	Quoted Cost	Savings	Supplier	Quoted Cost	Savings
Package 1	Name	$	$
Package 2	Name	$	$
Package 3	Name	$	$

Also, please note that the 'Rebate' field I have is an Inputfield and values can be entered into the 'Rebate' field in the table. So, in order to keep the table dynamic, I would prefer to have this 'Rank' dimension (which is based on the 'Savings,K' field, an expression) created in the front end rather than the script. Is there any way we can achieve this? Thanks in advance!

stalwar1‌

[mrthomasshelby]

What I have tried is add the following expression as a calculated dimension:

=if(aggr(rank(Sum(([Price1] - ([Quote Price]*(1-[Rebate])))*(Volume))/1000),Supplier)=1,'Rank 1',

if(aggr(rank(Sum(([Price1] - ([Quote Price]*(1-[Rebate])))*(Volume))/1000),Supplier)=2,'Rank 2',

if(aggr(rank(Sum(([Price1] - ([Quote Price]*(1-[Rebate])))*(Volume))/1000),Supplier)=3,'Rank 3',

if(aggr(rank(Sum(([Price1] - ([Quote Price]*(1-[Rebate])))*(Volume))/1000),Supplier)=4,'Rank 4',

if(aggr(rank(Sum(([Price1] - ([Quote Price]*(1-[Rebate])))*(Volume))/1000),Supplier)=5,'Rank 5'

)))))

which will become a bit cumbersome as the number of suppliers keep on increasing. Any better way to do this? Thanks in advance!

[sunny_talwar · Accepted solution]

May be this

='Rank ' & Aggr(Rank(Sum(([Price1] - ([Quote Price]*(1-[Rebate])))*(Volume))/1000),Supplier)

[mrthomasshelby]

Hey Sunny! As usual this is exactly what I was looking for! Thanks a ton! Much appreciated!