topic Re: Highlighting top 3 values in a pivot in QlikView

Highlighting top 3 values in a pivot

Fri, 06 Jan 2017 14:15:43 GMT

Hello

I need to highlight the highest 3 values in a column within a Pivot table. I know how to do it based on Max and Min Value, but is there a way to do it with the Max3? I have tried the following formula:

The below formula makes all of the cells red, not just the highest 3

If(Rank(Total Sum([Issue ID (Issue Inventory)])<= 3),RGB (255,0,0))

What am I doing wrong?

Re: Highlighting top 3 values in a pivot

sunny_talwar — Fri, 06 Jan 2017 15:12:19 GMT

Try this (you misplaced the closing parenthesis for Rank() function. It should be before the < sign and not after 3 (or 4 in my case):

If(Rank(Total Sum([Issue ID (Issue Inventory)])) < 4, RGB (255,0,0))

Re: Highlighting top 3 values in a pivot

Fri, 06 Jan 2017 15:22:42 GMT

Thank you, that helped a lot, but now I am not showing the top Values, I am not sure what the issue is now, any thoughts?

Formula:

If(Rank(Total Sum([Issue ID (Issue Inventory)]))<4,RGB (255,0,0))

This is what is happening with my data:

As you can see, the 395 should not be in red, it should be 510.

Re: Highlighting top 3 values in a pivot

sunny_talwar — Fri, 06 Jan 2017 15:28:37 GMT

Can you create another expression

Rank(Total Sum([Issue ID (Issue Inventory)]))

and check what rank is assigned to 510?

Re: Highlighting top 3 values in a pivot

Fri, 06 Jan 2017 15:34:26 GMT

Re: Highlighting top 3 values in a pivot

sunny_talwar — Fri, 06 Jan 2017 15:51:52 GMT

I think your expression for Total is not this

Sum([Issue ID (Issue Inventory)])

If you want to rank Total, then use the exact expression you have within Total column

Rank(Total 'Change this to whatever you have under Total')

Re: Highlighting top 3 values in a pivot

Fri, 06 Jan 2017 16:05:35 GMT

Hi Sunny:

I have tried this:

=Rank(Count([Issue ID (Issue Inventory)]))

My original formula was:

Count([Issue ID (Issue Inventory)])

But now with the updated formula, I have gone from showing the total to showing the ranking.

I cant do a SUM on the field, since there are actual numbers, I need to do a count.

This is what is happening to the column, with the new formula

Re: Highlighting top 3 values in a pivot

sunny_talwar — Fri, 06 Jan 2017 16:08:21 GMT

Your expression will be

Count([Issue ID (Issue Inventory)])

Backgrond expression

If(Rank(Total Count([Issue ID (Issue Inventory)]))<4,RGB (255,0,0))

Re: Highlighting top 3 values in a pivot

Fri, 06 Jan 2017 16:20:39 GMT

That worked like a dream! Thank you so much!!!