How do I exclude negative numbers from summing?

[pnn44794]

Hello:

I need to exclude negative numbers from summing.  My struggle is finding and using the right syntax.

Any and all help is appreciated.  Thanks in advance.

[Nicole-Smith]

You can add an item to your set analysis to ignore anything less than zero:

sum({$ <[Save Initiative Category]={Server\Storage}, [Finance Confirmed 2016 Savings]={'>0'}>} [Finance Confirmed 2016 Savings]) / 59500000

[swuehl]

The reason why your approach is not working is that you are doing the rangesum after the aggregation, while you need to  do it on a per record base, i.e. inside the aggregation:

sum({$ <[Save Initiative Category]={Server\Storage}>} Rangesum([Finance Confirmed 2016 Savings],0) ) / 59500000

Or follow Nicole's suggested set analysis.

[pnn44794]

Hello Nicole:

Thanks for the reply.  When I enter the formula in the Chart Expression, Qlikview believes there are errors in the expression.  It doesn't say what the error is or might be.  It just says "Errors in expression".  Starting with the left most [, everything has either a red squiggly line or an orange squiggly line under it (I'm using whatever the default color coding is for Qlikview).

[pnn44794]

Hello Stefan:

Thanks for the reply.  I have the same situation with your suggested formula as I do with Nicole's.  When I enter the formula in the Chart Expression, Qlikview believes there are errors in the expression.  It doesn't say what the error is or might be.  It just says "Errors in expression".  Starting with the left most [, everything has either a red squiggly line or an orange squiggly line under it (I'm using whatever the default color coding is for Qlikview).

[swuehl]

You don't have any issues with your original expression?

=sum({$ <[Save Initiative Category]={Server\Storage}>} [Finance Confirmed 2016 Savings]) / 59500000

[Save Initiative Category] should be a field name in your data model.

Server\Storage should not be a field name (at least, the syntax makes no sense using a field name as value list).

If you tell us, what you want to achieve with the original set expression, maybe we can help you finding the correct set expression more easily.

[pnn44794]

Hello Stefan:

I have no issue with the original expression except that it does not exclude negative numbers.

Thanks in advance.

[pnn44794]

I'm not sure if this matters or not, but the negative numbers are formatted like this example:

$          (21,189)

[sunny_talwar · Accepted solution]

1) It is strange that in your screenshot, QlikView is unable to identify Save Initiative Category. I say this because there is a red wiggly line underneath the field name.

When you use your original expression (mentioned below), do you still see that red wiggly line?

Sum({$ <[Save Initiative Category]={Server\Storage}>} [Finance Confirmed 2016 Savings]) / 59500000

Assuming, that red wiggly line is not an issue and its truly a field name. Can you try this:

Sum({$<[Save Initiative Category] = {Server\Storage}>} RangeMax([Finance Confirmed 2016 Savings], 0)) / 59500000

or

Sum({$<[Save Initiative Category] = {Server\Storage}>} If([Finance Confirmed 2016 Savings] > 0, [Finance Confirmed 2016 Savings])) / 59500000

[pnn44794]

Hello Sunny:

Qlikview does not have any issue with the original expression and I do not see a red wiggly line under any of it.  It is truly a field name.  So is Finance Confirmed 2016 Saves.

I'll try both formulas now and respond back quickly.