expression issue

[soniasweety]

Hi all,

@MayilVahanan 

i  am having one issue wit text colour in pivot table..  anything wrong im doing here?

IF(Only(Value)='S', Black(),
IF(sum(KA) $(=Only(Aggr(MinString(KCS),Year,Case,Des))) sum(KT),GREEN(),RED() ))   

this expression is not working perfectly on the last row of each category

[rubenmarin]

I would check with an expression like 

If(sum(KA)>=sum(KT),1,2)

If it returns '2' it's becuase the second part is really higher that the first part, it can be by some decimals value. You can try to enclose each aprt of the epression in a Round, like:

If(Round(sum(KA),0.01)>=Round(sum(KT),0.01),GREEN(),RED())

[soniasweety]

@rubenmarin  yeah  i checked already the expression its giving 2 . but result is showing wrong.

If we use round this condition will.apply to some values that have more than 1 decimal.. so it's not to use..

Is there any other function rather than round ? Please suggest.

IF(Only(Value)='S', Black(),
If(KCS='>='
,If(round(sum(KA),0.01)>=round(sum(KT),0.01),GREEN(),RED())
,If(KCS='<='
,If(sum(KA)<=sum(KT),GREEN(),RED())
)
)))

[rubenmarin · Accepted solution]

Hi @soniasweety,  if the expression returns 2 that means that the first value is lower than the second, so I can only think on Round(), Ceil() or Floor() to ignore values after x decimal, you can add more decimals, I think that Round([Expression], 0.0001) will also do the trick, this is just to compare and pick the color, the expression that shows he value doen't need this round.

You can also add 2 columns, one with Sum(KA) and the other with Sum(KT), add a lot of decimals to format number (14 is the maximum) and check wich one has the differnce, maybe it's on the 10th or even in the 14th decimal, this could happen because of the conversion of binary values to decimal values that sometimes can't give an exact value.

I can't think in other solution that some way of use rounded numbers to fix this.