11 Replies Latest reply: Sep 28, 2017 2:13 PM by Vishnu Chakravaram

# Color expression based on rank(measure)by row

Hi Can we color the background cell in a table

I want to rank the measure in for each(Title col) using colormix gradient.

I am trying to work with this code here

Colormix(aggr(rank(sum([# Apps])),[Row dimension]),,RGB(67,89,56),RGB(x,y,z)

sample data.

 title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec A 387 817 974 254 286 437 502 454 704 265 516 499 B 1,349 1,501 819 806 1,118 815 735 2,669 1,505 609 675 1,302 C 19,716 18,177 17,082 16,170 19,124 21,197 20,417 20,963 21,113 18,219 16,838 15,830

Something like attached.

Rank 1 in each row will be one color,rank 2 in each row will be second color etc.

thanks

• ###### Re: Color expression based on rank(measure)by row

Why not try like this (if this is only for 1st three ranks)

If(HRank(Sum([# Apps])) = 1, Color1,

If(HRank(Sum([# Apps])) = 2, Color2,

If(HRank(Sum([# Apps])) = 3, Color3)))

• ###### Re: Color expression based on rank(measure)by row

Thankyou Sunny- I have 12 months so i can define 12 ranks.What is difference between Hrank and Rank?

• ###### Re: Color expression based on rank(measure)by row

HRank is used for pivoted dimensions in a pivot table and Rank is used in Straight table or unpivoted dimensions. Also, read here:

HRank ‒ QlikView

• ###### Re: Color expression based on rank(measure)by row

Sunny -thanks again - the code worked.

• ###### Re: Color expression based on rank(measure)by row

Super

• ###### Re: Color expression based on rank(measure)by row

Sunny when I apply a date filters- i think the code is not working? i selected a date range and i see just one col colored.

If no date filter I see color on every cell.

this is my code

If(HRank(Sum([# Applications])) = 1, RGB(221,217,196),

If(HRank(Sum([# Applications])) = 2, RGB(197,217,241),

If(HRank(Sum([# Applications])) = 3, RGB(242,220,219),

If(HRank(Sum([# Applications])) = 4, RGB(235,241,222),

If(HRank(Sum([# Applications])) = 5, RGB(228,223,236),

If(HRank(Sum([# Applications])) = 6, RGB(218,238,243),

If(HRank(Sum([# Applications])) = 7, RGB(250,191,143),

If(HRank(Sum([# Applications])) = 8, RGB(196,215,155),

If(HRank(Sum([# Applications])) = 9, RGB(146,205,220),

If(HRank(Sum([# Applications])) = 10, RGB(230,184,183),

If(HRank(Sum([# Applications])) = 11, RGB(196,189,151),

If(HRank(Sum([# Applications])) = 12, RGB(191,191,191)))))))))))))

• ###### Re: Color expression based on rank(measure)by row

Try this

If(HRank(Sum({1}[# Applications])) = 1, RGB(221,217,196),

If(HRank(Sum({1}[# Applications])) = 2, RGB(197,217,241),

If(HRank(Sum({1}[# Applications])) = 3, RGB(242,220,219),

If(HRank(Sum({1}[# Applications])) = 4, RGB(235,241,222),

If(HRank(Sum({1}[# Applications])) = 5, RGB(228,223,236),

If(HRank(Sum({1}[# Applications])) = 6, RGB(218,238,243),

If(HRank(Sum({1}[# Applications])) = 7, RGB(250,191,143),

If(HRank(Sum({1}[# Applications])) = 8, RGB(196,215,155),

If(HRank(Sum({1}[# Applications])) = 9, RGB(146,205,220),

If(HRank(Sum({1}[# Applications])) = 10, RGB(230,184,183),

If(HRank(Sum({1}[# Applications])) = 11, RGB(196,189,151),

If(HRank(Sum({1}[# Applications])) = 12, RGB(191,191,191)))))))))))))

• ###### Re: Color expression based on rank(measure)by row

I think the earlier code is also working. the reason it highlighted only sept was i was choosing only sept dates. Sorry about that

I  understood the behavior after i included the another child diemension

. and thankyou for the code again

• ###### Re: Color expression based on rank(measure)by row

This will only simplify the nested if's

Pick (

HRank(Sum([# Applications])) , RGB(221,217,196), RGB(197,217,241), ("IN THIS ORDER")

)

• ###### Re: Color expression based on rank(measure)by row

Thanks Vishnu- this avoids the confusion on # of braces in nested if stmt.

• ###### Re: Color expression based on rank(measure)by row

Good to know

Also,

Alt(

Pick (

HRank(Sum([# Applications])) , RGB(221,217,196), RGB(197,217,241), ("IN THIS ORDER")

)

, BLACK ())

This should take care of a default color to be applied when there's no output for the Pick to resolve.