Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Challenge on Sum based on 2 criteria

Hi

I am struggling with setting up a Sum If based on 2 criteria. First Criteria is a Wildcard Search and Second Criteria is Value Less than Zero.

Example:  =SUM({<LOC_CODE={"TR*"},MVT_UNIT = {"<0"}>}MVT_UNIT)   --Result should be and is 1154

Example:  =SUM({<LOC_CODE={"TR*"},MVT_UNIT = {">0"}>}MVT_UNIT)   --Result should be 1196 and is 0

I have a location for stock movement and I need to Sum Values <0 in one column and Sum Values >0 in another column.

Please let me know if this needs further explanation.

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

If you're only summing negative values for the first one, how can the result be a positive number (1154)?

In any case, it's probably easier to write this using mathematical formulas rather than set analysis:

Sum only positive values:

=SUM({<LOC_CODE={"TR*"} >} RangeMax(MVT_UNIT,0)) 

Sum only negative values:

=SUM({<LOC_CODE={"TR*"} >} RangeMin(MVT_UNIT,0))

View solution in original post

2 Replies
Highlighted
Specialist III
Specialist III

If you're only summing negative values for the first one, how can the result be a positive number (1154)?

In any case, it's probably easier to write this using mathematical formulas rather than set analysis:

Sum only positive values:

=SUM({<LOC_CODE={"TR*"} >} RangeMax(MVT_UNIT,0)) 

Sum only negative values:

=SUM({<LOC_CODE={"TR*"} >} RangeMin(MVT_UNIT,0))

View solution in original post

Highlighted
Contributor
Contributor

Thank you