18 Replies Latest reply: Jul 27, 2016 11:22 AM by Sunny Talwar

# Color by Expression (Green - Red - Yellow)

Hello,

I have a color expression:

ColorMix2( (rank(total column(1))/(noofrows(TOTAL)/2))-1 ,green(), lightred(),yellow())

And it gives me the following (which I want):

But this is a drill-down and it's possible they'll keep clicking until there is only one bar. When there is only one bar - it defaults to the color red.

I don't want them to think that they were red so why did they change to red. Is there a way to write in the expression to default to a certain color with the drill-down?

• ###### Re: Color by Expression (Green - Red - Yellow)

Are you looking to keep the original color? Try this:

ColorMix2((Rank(TOTAL 'Replace this with the actual expression, but add {1} to the set analysis')/(NoOfRows(TOTAL)/2))-1, Green(), LightRed(), Yellow())

• ###### Re: Color by Expression (Green - Red - Yellow)

ColorMix2((Rank( TOTAL ColorMix2((rank(total column(1))/(noofrows(TOTAL)/2))-1 ,green(), lightred(),yellow()) )/(NoOfRows(TOTAL)/2))-1, Green(), LightRed(), Yellow())

Where does the {1} come in?

• ###### Re: Color by Expression (Green - Red - Yellow)

What is the expression that you are using for Column(1)?

• ###### Re: Color by Expression (Green - Red - Yellow)

Good morning Sunny,

They are drill-downs.

1 - Location

2 - District

3 - Person

the measure is showing % complete by each.

• ###### Re: Color by Expression (Green - Red - Yellow)

Still looking to see if this is possible. Any thoughts?

• ###### Re: Color by Expression (Green - Red - Yellow)

I would need to know what your expression is to help you any further . Sorry if you are not able to share that information.

• ###### Re: Color by Expression (Green - Red - Yellow)

Good morning Sunny,

They are drill-downs.

1 - Location

2 - District

3 - Person

the measure is showing % complete by each.

• ###### Re: Color by Expression (Green - Red - Yellow)

Is this expression or Dimension?

• ###### Re: Color by Expression (Green - Red - Yellow)

RangeSum(

Count({\$<[LEVEL 0 COMPLETION]={"Y"}, [LEVEL 1 COMPLETION]={"Y"}, [LEVEL 3 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID),

Count({\$<[Sales Manager\$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager\$.LEVEL 1 COMPLETION]={"Y"}, [Sales Manager\$.LEVEL 3 COMPLETION]={"Y"}, [Sales Manager\$.Position Code]={04,41}>}[Sales Manager\$.SID]),

Count({\$<[Service Manager\$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager\$.LEVEL 1 COMPLETION]={"Y"}, [Service Manager\$.LEVEL 3 COMPLETION]={"Y"}, [Service Manager\$.Position Code]={08,09,17,32,33,35}>}[Service Manager\$.SID])) /

RangeSum(

Count({<[Position Code]={12,42}>}SID),

Count({<[Sales Manager\$.Position Code]={04,41}>}[Sales Manager\$.SID]),

Count({<[Service Manager\$.Position Code]={08,09,17,32,33,35}>}[Service Manager\$.SID]))

• ###### Re: Color by Expression (Green - Red - Yellow)

ColorMix2((Rank(TOTAL

RangeSum(

Count({1<[LEVEL 0 COMPLETION]={"Y"}, [LEVEL 1 COMPLETION]={"Y"}, [LEVEL 3 COMPLETION]={"Y"}, [Position Code]={12,42}>}SID),

Count({1<[Sales Manager\$.LEVEL 0 COMPLETION]={"Y"}, [Sales Manager\$.LEVEL 1 COMPLETION]={"Y"}, [Sales Manager\$.LEVEL 3 COMPLETION]={"Y"}, [Sales Manager\$.Position Code]={04,41}>}[Sales Manager\$.SID]),

Count({1<[Service Manager\$.LEVEL 0 COMPLETION]={"Y"}, [Service Manager\$.LEVEL 1 COMPLETION]={"Y"}, [Service Manager\$.LEVEL 3 COMPLETION]={"Y"}, [Service Manager\$.Position Code]={08,09,17,32,33,35}>}[Service Manager\$.SID])) /

RangeSum(

Count({1<[Position Code]={12,42}>}SID),

Count({1<[Sales Manager\$.Position Code]={04,41}>}[Sales Manager\$.SID]),

Count({1<[Service Manager\$.Position Code]={08,09,17,32,33,35}>}[Service Manager\$.SID]))

)/(NoOfRows(TOTAL)/2))-1, Green(), LightRed(), Yellow())

• ###### Re: Color by Expression (Green - Red - Yellow)

That worked - Because there is the {1< does that mean all of the other BC will have to stay at the bottom when filtered on?

• ###### Re: Color by Expression (Green - Red - Yellow)

Not sure I understand your question. What is BC here?

• ###### Re: Color by Expression (Green - Red - Yellow)

D - Southwest  or B - Northeast for example. It's keeping all the dimensions at the bottom instead of just the one with the color that I am filtered on.

• ###### Re: Color by Expression (Green - Red - Yellow)

What version of Qlik Sense are you using right now?

• ###### Re: Color by Expression (Green - Red - Yellow)

2.2.3

• ###### Re: Color by Expression (Green - Red - Yellow)

I guess the issue should go away if you can upgrade to QS 3 and above.

• ###### Re: Color by Expression (Green - Red - Yellow)

Okay. Thanks! Our company won't be doing that for a small period of time. Thanks for your help.

• ###### Re: Color by Expression (Green - Red - Yellow)

No problem