Expression Query

[AnkitaC]

hi all,

I am trying sum(diffrence)<0 then sum(difference) and using 
=If (sum([difference])<'0',sum(difference))

this expression using this showing correct result but top of the column they are taking sum(difference ) al values not only <0 sum

please help me to write this expression qlik sense measure

thank you,

ankita

please check below image column values are correct but at the sum of total top they taking all sum(difference ) all difference not only less than < 0 difference

[RafaelBarrios · Accepted solution]

hi @AnkitaC 

this is what happen

when use a dimension in the table, Qlik will evaluate "If (sum([difference])<'0',sum(difference))" by each of the dimesion values.

while, for total it will evaluate all records not considering the dimension, and if the global  is <0, then it will make the sum()

here an example

LOAD * inline [
Product, Store, difference
productA,StoreA,0
productA,StoreB,-500
productA,StoreC,100
productB,StoreC,-700
productB,StoreC,-100
productC,StoreA,200
]; 

*its only ignoring "productC" and as you dont have the Store as dimension, it evaluate by product and the make the sum()

so, you have two option here

#1 change the total function for the column

#2 change your formula so it evaluate row by row

in this case it will ignore also productA,StoreC,100 as its >0 from product/store combination

hope this helps,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 😁

[AnkitaC]

Hi,

Thank you for reply

sum(aggr(if(sum(difference)>0,sum(difference),0),field_name, field_name))

this expression is work for me