31 Replies Latest reply: May 21, 2015 11:04 AM by Andrea Longhi

# Color code rows in dimension value

Hello

I would like to color code the background of the dimension values in a straight table, somewhat like the "strip every" function but for every new value. With the following example table;

Dimension1Dimension2Expression
A1
A2
A3
B1
B2
C1
D1
D2
D3
D4

I would like the background color of Dimension1 to be grey for the values A, white for B, grey for C, white for D and so on.

Is this possible? I have tried with the above() function but that doesn't seems to do the trick. The values of Dimension1 in the real data is not as simple as A-Z.

• ###### Re: Color code rows in dimension value

You can click on the '+' symbol next to your dimension and there under 'background' option, you can write a if statement to derive the color you want.

I hope this helps.

• ###### Re: Color code rows in dimension value

Yea, I know how to change the background color of the cell - the question I asked was how to give the the Dimension1 value A and C the same bg color, and B and D another bgcolor.

• ###### Re: Color code rows in dimension value

Hi,

This should do the trick :

Background Color expression for Dimension1 :

=if(even(aggr(NODISTINCT rowno(),Dimension1)),rgb(180,180,180),white())

Regards,

Thomas

• ###### Re: Color code rows in dimension value

We're getting close, thanks Thomas. However it looks like i need to combine some sort of sort with the aggr() function, as of now the first set of dimension1 values in the straight table doesn't get the number 1, and the second set of values doesn't get the number 2.

• ###### Re: Color code rows in dimension value

Yes, you're right, rowno() order seems to be inverted (max value for first dimension value).

That's start to become tricky, but i think the following expression could make it :

=if(even(aggr(NODISTINCT GetPossibleCount(Dim1) - rowno(),Dim1)),white(),rgb(180,180,180))

• ###### Re: Color code rows in dimension value

Well im my test app the "sort order" is somewhat fucked - i could live with an inverted sort order but not with the an "unknown order".

Attached the test app.

• ###### Re: Color code rows in dimension value

Yea, rowno() seems to use kind of fieldindex values, we cannot sort them.

I've changed my mind, so, last try...

With a variable : vTEST = concat(DISTINCT TOTAL {<Dim2-={"\$(=null())"}>} chr(39)&Dim2&chr(39),',')     //Need to remove null values, don't know why

and as color expression : =if(even(match(Dim2,\$(vTEST))),white(),rgb(200,200,200))

Hope this one will be the good one...

Thomas

• ###### Re: Color code rows in dimension value

Hi Blaise,

There is a function called HSL(Hue,Saturation,lum) where we can pass the values from 0 to 1.

If you are going to generate one unique HSL for each distinct value of Dim1, you can use the value in Background colour.

Here is the sample code that i attached. Please reply back if it works.

• ###### Re: Color code rows in dimension value

I don't want unique color for every value

• ###### Re: Color code rows in dimension value

I've tried it but sadly it doesn't work. Will try more with the aggr(), match() and concat() function though...

• ###### Re: Color code rows in dimension value

Hi Blaise,

My last proposition is working weel i think, see attachement.

3 remarks :

- Don't forget the "=" sign before the variable expression

- if you have "set analysis" in the expressions of your table that could reduce the values of Dim2, use the same SET in the concat function of the variable

- The sort order of Dim2 must be fixed. If it's not sorted alphabeticaly, you can also define a custom sort order in the concat function

Good luck,

Thomas

• ###### Re: Color code rows in dimension value

Yes indeed, it works!

I would rather not have a variable that is calculated for every selection, but it might be that this is the only available solution .

• ###### Re: Color code rows in dimension value

Hi Blaise,  my suggestion worked ?

• ###### Re: Color code rows in dimension value

Hi Johan,

I would be really interested in knowing whether you've managed to get it working with a custom sort and whether it persists when you make selection changes that influences the table records?

Regards,

Sean

• ###### Re: Color code rows in dimension value

Well Thomas Duvivier's last suggestion was indeed working but with a variable that is calculated for every change in selections. Not a solution I was hoping for so I dropped it in the end.

• ###### Re: Color code rows in dimension value

Ah ok I see, thanks for the reply.

• ###### Re: Color code rows in dimension value

I had a similar requirement with the straight table where I wanted to highlight only that dimension value which user selects form in the list box. Achieved this using match function along with GetFieldSelections.

• ###### Re: Color code rows in dimension value

Hi Nakul,

Let me know if it solve your needs.

Best regards

Andrea

• ###### Re: Color code rows in dimension value

Hi Blaise, Please find the attched file and hope it helps very well.

• ###### Re: Color code rows in dimension value

It works with no selection, but if you for example select Dim2 = A,C then all rows are grey. I don't think it can be solved in the script.

• ###### Re: Color code rows in dimension value

I defined the coulr for each value ofDim1 in the Script. So you selected any thing, automatically selection made.

• ###### Re: Color code rows in dimension value

Hi blaise,  please try this:  1. open the table property window 2. select the dimension tab  3. click the plus (+) sign on the left of the dimension to fill with different colour 4. double click on background colour 5. enter your conditional fill expression [eg. =if(Dimension1 like 'A*',QlikTechBlue(),if(Dimension1 like 'B*',QlikTechGray(),Green())) ] 6. confirm changes  Regards Andrea

• ###### Re: Color code rows in dimension value

doesn't solve my problem. I don't want to hard code colors to each dimensions.

• ###### Re: Color code rows in dimension value

is it possible for you to store the color (RGB combination) as part of your table? If YES, then in your table itself you can define which color you want for each dimension value (A, B, C etc..).

Then you can use the RGB values in your table object to define the color of the cell based on cell value.

• ###### Re: Color code rows in dimension value

Hi Johan

please look at attached qv app and say me if it solve your problem.

Best regards

Andrea

• ###### Re: Color code rows in dimension value

Hi blaise,  try putting the following expression in the color property of the dimension:  =if(Mod(FieldIndex('Dimension1',Dimension1),2)=1,QlikTechGray(),White())  Regards AL

• ###### Re: Color code rows in dimension value

The fieldIndex() function doesn't return a sorting that can be used - as explained earlier in the thread.

• ###### Re: Color code rows in dimension value

In the 'Background color' box, type in something like the following:

=if([Dimension 1]='A' or [Dimension 1]='C'

,rgb(255,255,255)

,rgb(224,224,224)

)

you can add as many values as you want, by adding 'or' statements.