How to change the text color of the value depending on the comparison between values from two data sources in Qlik Sense

[janus2021]

Hi.

I want to change the background color depending on whether the two input fields match each other or not.

source 1: as Source1_value
cod1: 700
cod1: 300
cod1: 1000
cod2: 1000
cod3: 1000

source 2: as Source2_value
cod1: 2000
cod2: 400
cod3: 1000

Calculation:

Sum ({<[Name] =, code = {'code1'}>} Source1_value) -Sum ({<[name] =, code = {'code1'}>} Source2_value)
Sum ({<[Name] =, code = {'code2'}>} Source1_value) -Sum ({<[name] =, code = {'code2'}>} Source2_value)
Sum ({<[Name] =, code = {'code3'}>} Source1_value) -Sum ({<[name] =, code = {'code3'}>} Source2_value)

Output in a table:
Diff cod1,  Diff cod2,  Diff cod3
0                      600               0

What I want is that the number under "Diff cod1" should have a different color, even if the sum of the calculation is 0. so if it differs in the values between the different sources then it should be highlighted.
So the number under "Diff cod1" should have a different color while the number under "Code3 diff" should remain black, because the input values are the same from both sources.

This is done in the field: text color expression:
Tried with: if (count ({<[Source1_value]>} Source2_value) <> 0, red (), black ()) but it does not work. Then it will not be entirely correct to just count the number of entries.

[Andrei_Cusnir · Accepted solution]

Hello,

If my understanding is correct, you would like to achieve the following use case scenario:

• The first 0 below "Diff cod1" should be RED. Because 300+700+1000 - 2000 = 0 and (300, 700, 1000) is not equal with (2000)
• The second 600 below "Diff cod2" should be RED. Because 1000 - 400 = 600 and (1000) is not equal with (400)
• The last 0 below "Diff cod3" should be BLACK. Because 1000 - 1000 = 0 and (1000) is equal with (1000)

In that case you can use the function Concat() to compare the values on both sides as string. In case my following example doesn't help you resolve the issue 100%, you can use the logic and modify the expressions based on your needs.

On my side I have the following setup:

1. Loaded dataset:

2. Then I have created the following Table chart:

3. I have used the following expressions in background color:

• if(Concat( {<code={"code1"}>} Source1_value,',') = Concat( {<code={"code1"}>} Source2_value,','), Black(), Red())
• if(Concat( {<code={"code2"}>} Source1_value,',') = Concat( {<code={"code2"}>} Source2_value,','), Black(), Red())
• if(Concat( {<code={"code3"}>} Source1_value,',') = Concat( {<code={"code3"}>} Source2_value,','), Black(), Red())

This will concatenate all the values together based on the set analysis each time and then will compare them as a string. For example this is the breakdown of the calculations:

• First calculation will be "1000,300,700" = "2000" is NOT equal so result is FALSE. Therefore, it will be RED.
• Second calculation will be "1000" = "400" is NOT equal so result is FALSE. Therefore, it will be RED.
• First calculation will be "1000" = "1000" IS equal so result is TRUE. Therefore, it will be BLACK.

4. My outcome is:

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

[janus2021 · Accepted solution]

Hi.

I seems to work, thanks.