Change row color when change value

[Igor_Ribeiro]

I have the following table:

Row No	RMA No	Sequence No	From Status	To Status
1	BRH1N10021	1	Keying	Repair
2	BRH1N10021	2	Repair	Ship
3	BRH1N10021	3	Ship	Closed
4	BRH1N20034	1	Keying	Repair
5	BRH1N20034	2	Repair	Ship
6	BRH1N40012	1	Keying	Repair
7	BRH1N40025	1	Keying	Repair

My desired result is to alternate between background colors when the "RMA No" value changes. Example (consider the font color as the background color):

Row No	RMA No	Sequence No	From Status	To Status
1	BRH1N10021	1	Keying	Repair
2	BRH1N10021	2	Repair	Ship
3	BRH1N10021	3	Ship	Closed
4	BRH1N20034	1	Keying	Repair
5	BRH1N20034	2	Repair	Ship
6	BRH1N40012	1	Keying	Repair
7	BRH1N40025	1	Keying	Repair

[durgesh22]

use below expression in "Text Color Expression" configuration of your table chart:

if (floor(random) >0 and floor(random)<10 , RGB(204,229,255),
if (floor(random) >10 and floor(random)<20 , RGB(102,204,0),
if (floor(random) >20 and floor(random)<30 , RGB(233,50,0),
if (floor(random) >40 and floor(random)<50 , RGB(255,0,120)))))

[stevedark · Accepted solution]

Hi @Igor_Ribeiro 

The code that you want for the background colour on each of your cells is:

if(mod(match([RMA No], '$(=concat(distinct [RMA No], chr(39) & ',' & chr(39)))'),2) = 0, rgb(230,230,255), rgb(240,240,240))

This will give you something like this:

I've attached the app that I tested this with, should you need it.

Now, for the reason why it works!

The CONCAT statement will give you a list of all values grouped together. We use $(= ) to calculate this outside of the chart, to consider all dimension values. The DISTINCT statement means each one appears only once and the parameter with chr(39) & ',' & chr(39) comma separates the values, and the opening and closing quotes are outside the dollar expansion. This gives us a comma separated list, which we can do a match of the current dimension value, and that will return the RMA's position in the list. Doing a mod 2 of that will return either 0 or 1 - that is then used in an IF statement to get the colour.

It relies on the order of the CONCAT values and the order of the table being the same, but if you are sorting by RMA then that should be the case.

Hope that this gives you what you need!

Cheers,

Steve

https://www.quickintelligence.co.uk/blog

[Igor_Ribeiro]

Thank you! This works but I must point out that only if you order the table by RMA No and/or RowNo

[stevedark]

Hi @Igor_Ribeiro 

Yes, I was aware that the sort order needs to be fixed in order for it to work properly. Sadly there is no option to fix the sort order. It was an option in QlikView (Sense's older sibling), so it's a real shame that it hasn't carried through into Sense. Qlik's desire to make things end-user configurable removes some required control from application designers. The fact you can't lock pivot tables tends to be even more of a pain, in my opinion.

Thanks for marking my solution as correct, as this helps other Community users.

Cheers,
Steve