Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: Expression Query

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

AnkitaC

Contributor II

2023-09-16
04:08 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

207 Views

1 Solution

Accepted Solutions

RafaelBarrios

Partner - Specialist

2023-09-19
12:14 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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! 😁**

178 Views

2 Replies

RafaelBarrios

Partner - Specialist

2023-09-19
12:14 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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! 😁**

179 Views

AnkitaC

Contributor II

2023-09-20
02:28 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

Thank you for reply

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

this expression is work for me

167 Views

Community Browser