topic Re: Create a Rank as a Dimension in Pivot Table in QlikView

Create a Rank as a Dimension in Pivot Table

mrthomasshelby — Sun, 04 Mar 2018 05:47:56 GMT

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‌

Re: Create a Rank as a Dimension in Pivot Table

mrthomasshelby — Sun, 04 Mar 2018 06:02:05 GMT

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!

Re: Create a Rank as a Dimension in Pivot Table

sunny_talwar — Sun, 04 Mar 2018 12:38:25 GMT

May be this

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

Re: Create a Rank as a Dimension in Pivot Table

mrthomasshelby — Mon, 05 Mar 2018 05:12:08 GMT

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