Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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