topic Highlight highest Expressions in a pivot table per row in QlikView

Highlight highest Expressions in a pivot table per row

Fri, 09 Sep 2011 07:47:40 GMT

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......

Hope someone has a good suggestion!!

Thx in advance

Cheers Felix

Highlight highest Expressions in a pivot table per row

its_anandrjs — Fri, 09 Sep 2011 08:01:50 GMT

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

Highlight highest Expressions in a pivot table per row

Fri, 09 Sep 2011 10:14:27 GMT

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

Re: Highlight highest Expressions in a pivot table per row

Fri, 09 Sep 2011 10:42:45 GMT

Hi Felix,

see if this is what u want..

C u,

Stefano.

Re: Highlight highest Expressions in a pivot table per row

swuehl — Fri, 09 Sep 2011 10:44:47 GMT

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

Re: Highlight highest Expressions in a pivot table per row

swuehl — Fri, 09 Sep 2011 10:49:11 GMT

Hi Stefano,

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

Regards,

Stefan

Re: Highlight highest Expressions in a pivot table per row

Sat, 10 Sep 2011 17:40:02 GMT

Hi Stefano and Stefan,

thx for your efforts!

The solution of Stefano suited me best...

Thx again.

cheers

Felix

Re: Highlight highest Expressions in a pivot table per row

its_anandrjs — Sun, 11 Sep 2011 06:00:17 GMT

Hi Stefano,

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

Rgds

Anand