Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shruthibk
Creator
Creator

Rank in Pivot table

Hi, We have a requirement to highlight the top 5 and bottom 5 values in the pivot table. We can use the rank function to get the top 5 but I am unable to understand how to write this function for the cross table format of the pivot table.

I have attached the screenshot of the sample data with the expected output.

shruthibk_1-1594637372402.png

I searched before posting but did not get the info.

Thanks in advance.

 

 

1 Solution

Accepted Solutions
sunny_talwar

You might have to use Aggr() function here

Aggr(If(Rank(TOTAL Sum(Measure)) < 6, Green(), If(Rank(TOTAL -Sum(Measure)) < 6, Yellow())), Maanger, Category)

View solution in original post

6 Replies
Kushal_Chawda

Do you have any other dimension in pivot table?  If so how would you want to show Top5?

shruthibk
Creator
Creator
Author

 I don't have any other dimension, I just need to highlight the top5 and bottom 5 values.

in the attachment, i have highlighted the top 5 in green and bottom 5 in the amber color the same way I want to show.

sunny_talwar

You might have to use Aggr() function here

Aggr(If(Rank(TOTAL Sum(Measure)) < 6, Green(), If(Rank(TOTAL -Sum(Measure)) < 6, Yellow())), Maanger, Category)
Kushal_Chawda

Assuming you have two dimensions of which one is dragged to column. You can write the below expression in background color

 

Assuming you have Sum(Value) as your expression

=if(sum(Value)>= max(total aggr(sum(Value),Category,Name),4),green(),
if(sum(Value)<= min(total aggr(sum(Value),Category,Name),4),red()))

Kushal_Chawda

@sunny_talwar  gotcha!!!

shruthibk
Creator
Creator
Author

Thanks Sunny