# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Highlighted
Contributor III

## Create a Rank as a Dimension in Pivot Table

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!

1 Solution

Accepted Solutions
MVP

## Re: Create a Rank as a Dimension in Pivot Table

May be this

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

3 Replies
Contributor III

## Re: Create a Rank as a Dimension in Pivot Table

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!

MVP

## Re: Create a Rank as a Dimension in Pivot Table

May be this

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

Contributor III

## Re: Create a Rank as a Dimension in Pivot Table

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