Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Re: Expression Query

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

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

Labels (1)
• ### expression

1 Solution

Accepted Solutions
Partner - Specialist

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, differenceproductA,StoreA,0productA,StoreB,-500productA,StoreC,100productB,StoreC,-700productB,StoreC,-100productC,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😁

2 Replies
Partner - Specialist

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, differenceproductA,StoreA,0productA,StoreB,-500productA,StoreC,100productB,StoreC,-700productB,StoreC,-100productC,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😁

Contributor II
Author

Hi,