Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
mrthomasshelby
Contributor III

Create a Rank as a Dimension in Pivot Table

Hello,

I have a pivot table as follows:

Table3.png

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

1 Solution

Accepted Solutions

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
mrthomasshelby
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!

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)

mrthomasshelby
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!