Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator 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
sunny_talwar

May be this

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

View solution in original post

3 Replies
mrthomasshelby
Creator III
Creator III
Author

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

May be this

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

mrthomasshelby
Creator III
Creator III
Author

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