topic Re: Background Color Expression issue in App Development

Background Color Expression issue

mamaewapolya — Tue, 24 Jan 2023 10:38:59 GMT

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

Re: Background Color Expression issue

Digvijay_Singh — Mon, 24 Oct 2022 21:39:44 GMT

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

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

[MS1 Set]

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

Re: Background Color Expression issue

mamaewapolya — Tue, 25 Oct 2022 06:34:24 GMT

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.

Re: Background Color Expression issue

mamaewapolya — Tue, 24 Jan 2023 10:45:24 GMT

The solution is as follows:

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