Highlight highest Expressions in a pivot table per row

[#1]

Gents,

I've a problem with highlighting the background of an expressions of the 3 highest %'s per ROW.

Per Column it's no problem... when I use Column(1) instead of the part after RANK (between the brackets)

In the properties of the background setting I use the following formula:

=if(rank(sum({$<Country>} Turnover) / sum(total<Branche> Turnover)) < 4, Red())

Country|Branche| 0001 | 0002 | 0003 | 0004 |  0005 | 0006 |

A                3%      2%     1%     1%      4%      5%

B               5%      7%     11%    2%      4%     15%

C and so on......

D

Hope someone has a good suggestion!!

Thx in advance

Cheers Felix

[its_anandrjs]

Hi,

What is Country, Branche in the expression also please go through your expression once again or check it it is ok.

I think Country, Branche are fields.If you have any sample so provide to me.

HTH

Regards,

Anand

[#3]

Hi All,

maybe I did not explain the my issue well enough.

It's about highlighting the 3 highest values PER ROW.

The formula that I use in the settings of the expression in order to make the background of the cell red is the above one and might as well be:

=if(rank(column(1)) < 4, Red())

The point is that this generates a result with the highest 3 values highlighted per COLUMN.

Someone has an idea how to arrange this??

Cheers

Felix

[#4 · Accepted solution]

Hi Felix,

see if this is what u want..

C u,

Stefano.

[swuehl]

Hi fsnijders,

besides changing the orientation of the pivot I could think of something like this as expression for the expression format:

=if(aggr(rank(sum( Quantity) / sum(total<productID> Quantity),3),productID,Eff_Date) < 4, lightRed())

Please see also attached app (I have not changed my field names to yours, but that should be trivial).

Regards,

Stefan

[swuehl]

Hi Stefano,

Haven't thought about the hrank(), good idea!

Regards,

Stefan

[#7]

Hi Stefano and Stefan,

thx for your efforts!

The solution of Stefano suited me best...

Thx again.

cheers

Felix

[its_anandrjs]

Hi Stefano,

Nice explanation of Hrank( ) its good to see, never know about this before.

Rgds

Anand