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

[morgankejerhag]

Hi! Your expression contains some errors and some questions 🙂 What are you trying to achieve?

[MayilVahanan]

HI @soniasweety 

Can you please provide the sample qv file and expected output?

[soniasweety]

Hi @MayilVahanan   @Vegar  @morgankejerhag   @rubenmarin  the issue is   we are getting  <= from the field   KCS   

when i use  direct value  <=  its working fine.. but  when use below expression in place of it not working..

$(=Only(Aggr(MinString(KCS),Year,Case,Des)))

.   so how can we modify this expression?  anything is missing?

[rubenmarin]

Hi @soniasweety, as pointed by @morgankejerhag there are some errors in your initial post expression.

Also in this one there can be some inconsistences, in example, aggr will return one value for each Year-Case-Des, so this returns many values, and the Only expression is intended for expressions that returns only one value, maybe you want another Minstring() instead of the Only().

So I agree with @morgankejerhag  and @MayilVahanan, it will be better if you explain what you have, the result you want and why, and you can have a better answer if you provide a sample so we can work on it.

Rregards.

[soniasweety]

@rubenmarin    thank you for your input i tried with One more minstring its working fine.  like below

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

but i want to Optimize this formulae .   instead of multiple if's is there any way to optimize ?    any thoughts ? @MayilVahanan  @Vegar 

[rubenmarin]

Hi @soniasweety, I would double-check if that expression is really what you need, I think it will always return <=.

Also, in this case I don't think you can optimize the If's, as you need to do those checks, maybe do in the other way but the performance will be similar.

The $(=) part maybe can be optimized using only: $(=Minstring(KCS))

[soniasweety]

Yeah. @rubenmarin   here one more issue we found that,

the logic should work    based on comparision sign   of actual  vs target  then it should show green else red.

how can i modify this?  any suggestions?

one more thing - when i selects individual values its showing correct color .. but by default pivot table showing wrong color for some values. 

[rubenmarin]

Hi @soniasweety, maybe adding more if's:

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

[soniasweety]

Hi @rubenmarin  its working fine for all values except this  one value.   

for this condition its not showing green  even the value is "="  

so do we miss anything here ? we need to change any format or something?   rest all values showing  perfect except this.. 

we are using one input excel file for this