Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
soniasweety
Master
Master

expression issue

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
Labels (1)
1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

12 Replies
morgankejerhag
Partner - Creator III
Partner - Creator III

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?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
soniasweety
Master
Master
Author

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
Master
Master
Author

@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
Master
Master
Author

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
Master
Master
Author

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

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

sign.PNG

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   

input_excel.PNG