Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
AnkitaC
Contributor II
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

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

Labels (1)
1 Solution

Accepted Solutions
RafaelBarrios
Partner - Specialist
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, difference
productA,StoreA,0
productA,StoreB,-500
productA,StoreC,100
productB,StoreC,-700
productB,StoreC,-100
productC,StoreA,200
]; 

RafaelBarrios_0-1695139630392.png

*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

RafaelBarrios_1-1695139797449.png

 

#2 change your formula so it evaluate row by row

RafaelBarrios_2-1695139861259.png

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😁

 

 

 

View solution in original post

2 Replies
RafaelBarrios
Partner - Specialist
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, difference
productA,StoreA,0
productA,StoreB,-500
productA,StoreC,100
productB,StoreC,-700
productB,StoreC,-100
productC,StoreA,200
]; 

RafaelBarrios_0-1695139630392.png

*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

RafaelBarrios_1-1695139797449.png

 

#2 change your formula so it evaluate row by row

RafaelBarrios_2-1695139861259.png

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
Contributor II
Contributor II
Author

Hi,

Thank you for reply

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

this expression is work for me