topic Re: Rank in Pivot table in QlikView

Rank in Pivot table

shruthibk — Wed, 25 Nov 2020 16:16:04 GMT

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.

I searched before posting but did not get the info.

Thanks in advance.

Re: Rank in Pivot table

Kushal_Chawda — Mon, 13 Jul 2020 10:59:45 GMT

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

Re: Rank in Pivot table

shruthibk — Mon, 13 Jul 2020 11:13:44 GMT

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.

Re: Rank in Pivot table

sunny_talwar — Mon, 13 Jul 2020 11:45:06 GMT

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)

Re: Rank in Pivot table

Kushal_Chawda — Mon, 13 Jul 2020 11:46:58 GMT

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()))

Re: Rank in Pivot table

Kushal_Chawda — Mon, 13 Jul 2020 11:52:32 GMT

@sunny_talwar gotcha!!!

Re: Rank in Pivot table

shruthibk — Mon, 13 Jul 2020 13:53:09 GMT

Thanks Sunny