How to highlight background using Rank

[#1]

Hi,

I have a table with City Product Segment and Amount. Each product segment has a color coding. I want to change background color for each of the city based on max. business done per city  within the product segment.

Please find the sample qvw. The sample shows chart 1 with City and Amount. Chart 2 shows City, Product segment,amount and rank.

As per the sample data City 1 cell color in chart 1 should have yellow color as Prod 2 is ranked 1. Similarly City 2 should have green color as Prod 1 is ranked 1.

Tried a couple of options using rank not able to get it though. Thanks in advance.

Regards,

Lax

[jerem1234]

Please see if attached is what you are looking for. Ended up using the formula:

pick(match(firstsortedvalue(PRODUCT_SEGMENT, aggr(rank(sum(AMOUNT)), CITY, PRODUCT_SEGMENT)), 'prod1', 'prod2', 'prod3'), Green(), Yellow(), Red())

Hope this helps!

[CELAMBARASAN]

Try this

=Pick(Rank(sum(AMOUNT)), Green(), Yellow(), Red())

[#4]

Thanks Jerem,  The solution is alright if I have limited product, how do I manage if I have more than 20 products.

Regards

Lax

[#5]

Hi Celam,

Pick(Rank(sum(AMOUNT)), Green(), Yellow(), Red()) does not work If you have same product ranking 1 in 2 cities. In one city it makes green and in another city for the same product it makes red.

Regards.

Lax

[CELAMBARASAN]

I understood wrongly, Please check the solution below.

LOAD * Inline [

CITY, PRODUCT_SEGMENT, AMOUNT

city1,prod1,100

city1,prod2,200

city1,prod3,150

city2,prod1,250

city2,prod2,200

city2,prod3,210

city3,prod1,100

city3,prod2,50

];

LOAD * Inline [

PRODUCT_SEGMENT, R, G, B

prod1, 27, 169, 5

prod2, 245, 241, 0

prod3, 245, 50, 10];

Use inline loads as above and backgroun color expression

=RGB(FirstSortedValue(R, -Aggr(Sum(AMOUNT), CITY, PRODUCT_SEGMENT)), FirstSortedValue(G, -Aggr(Sum(AMOUNT), CITY, PRODUCT_SEGMENT)),

FirstSortedValue(B, -Aggr(Sum(AMOUNT), CITY, PRODUCT_SEGMENT)))