Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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! 😁
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! 😁
Hi,
Thank you for reply
sum(aggr(if(sum(difference)>0,sum(difference),0),field_name, field_name))
this expression is work for me