Background Color Expression issue

[mamaewapolya]

Hi everyone,

I am trying to implement the conditional colouring to the table, but it works for the red colour only. 

What I want to see as a result is that each value in a column, that is less than the average for this column, should be coloured in red and the others - in green.

if(Avg(Aggr(Sum({$<MS1Count=-{0}>}ORDERCOUNTER) / Sum(ORDERCOUNTER), OrderBranch)) >    
	[MS1 Set],                                                                                           
		Green(),LightRed())

MS1 Set is a master measure, which equals to Sum({$<MS1Count=-{0}>}ORDERCOUNTER) / Sum(ORDERCOUNTER)

If I replace MS1 Set with a value (e.g 0.2), the colouring rule works fine.

Could you please help me find the issue?

Thank you,

Polly

[Digvijay_Singh]

You may verify if measures are good by creating a test straight table and have these two measures - 

1. 

Avg(Aggr(Sum({$<Milestone1Count=-{0}>}SHIPMENTCOUNTER) / Sum(SHIPMENTCOUNTER), ShipmentBranch))

2. 

[MS1 Set]

I am guessing you would need aggr by same dim in 2nd measure as you have in first but not sure..

[mamaewapolya]

Hi @Digvijay_Singh ,

I have verified them in the table, that's why I am wondering why the colouring rule is not applied in a correct way.

I've just understood one thing - the [MS1 Set] value should be the same (fixed like in Excel) for all cells for one column. I am not sure it's possible in Qlik Sense though.

[mamaewapolya · Accepted solution]

The solution is as follows:

if(Sum({$<MS1Count=-{0}>} Total ORDERCOUNTER) / Sum(Total ORDERCOUNTER) >    
[MS1 Set],                                                                                      Green(),LightRed())