topic Re: How do I exclude negative numbers from summing? in QlikView

How do I exclude negative numbers from summing?

pnn44794 — Sun, 13 Oct 2019 19:42:30 GMT

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.

Re: How do I exclude negative numbers from summing?

Nicole-Smith — Fri, 09 Dec 2016 23:01:58 GMT

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

Re: How do I exclude negative numbers from summing?

swuehl — Sat, 10 Dec 2016 23:11:29 GMT

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.

Re: How do I exclude negative numbers from summing?

pnn44794 — Sun, 13 Oct 2019 19:43:56 GMT

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).

Re: How do I exclude negative numbers from summing?

pnn44794 — Sun, 13 Oct 2019 19:44:52 GMT

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).

Re: How do I exclude negative numbers from summing?

swuehl — Sun, 11 Dec 2016 20:53:17 GMT

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.

Re: How do I exclude negative numbers from summing?

pnn44794 — Sun, 13 Oct 2019 19:46:09 GMT

Hello Stefan:

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

Thanks in advance.

Re: How do I exclude negative numbers from summing?

pnn44794 — Sun, 11 Dec 2016 21:31:01 GMT

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

$ (21,189)

Re: How do I exclude negative numbers from summing?

sunny_talwar — Sun, 11 Dec 2016 21:31:18 GMT

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

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

Re: How do I exclude negative numbers from summing?

pnn44794 — Sun, 11 Dec 2016 21:36:28 GMT

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.

Re: How do I exclude negative numbers from summing?

pnn44794 — Sun, 11 Dec 2016 21:46:12 GMT

Hello Sunny:

I get the same result with your RangeMax option as I did with Stefan's and I get the same result using the if option. The gauge pegs out at 150% which is not correct. It should be 73.5%. I also have the red wiggly lines.

Re: How do I exclude negative numbers from summing?

swuehl — Sun, 11 Dec 2016 21:46:39 GMT

As Sunny pointed out, you should have used RangeMax() [like in your original approach] instead of RangeSum() as I 've written - but this should not cause the issue you are experiencing.

Would it be possible that you upload a small sample QVW to look at?

Re: How do I exclude negative numbers from summing?

sunny_talwar — Sun, 11 Dec 2016 21:50:02 GMT

What number you get when you try this in a text box object?

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

Is the number larger that 59,500,000?

Re: How do I exclude negative numbers from summing?

pnn44794 — Sun, 13 Oct 2019 19:35:47 GMT

Ok.

Re: How do I exclude negative numbers from summing?

sunny_talwar — Sun, 11 Dec 2016 22:18:28 GMT

Red wiggly line is definitely a concern for me. I can't seem to find a reason for why it would continue to show up. Would you be able to share a sample just like Stefan mentioned? Or if not, can you share a screenshot of expression editor where you use your original expression

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

I am curious to see why this one doesn't show a wiggly red line under the Save Initiative Category.

Re: How do I exclude negative numbers from summing?

pnn44794 — Sun, 13 Oct 2019 19:36:29 GMT

Hold on. Please ignore those numbers. There was a filter set that I missed.

Re: How do I exclude negative numbers from summing?

sunny_talwar — Sun, 11 Dec 2016 22:24:24 GMT

So then its working now?

Re: How do I exclude negative numbers from summing?

pnn44794 — Sun, 13 Oct 2019 19:37:14 GMT

No, it is still not working.

Re: How do I exclude negative numbers from summing?

sunny_talwar — Sun, 11 Dec 2016 22:27:23 GMT

It seems that there are two spaces between Initiative and Category and your other expressions only had one space between them. Can you add another space for the other expressions you have been trying out?

Re: How do I exclude negative numbers from summing?

pnn44794 — Sun, 11 Dec 2016 22:33:49 GMT

Holy Cow!!!! That's it. I added the extra space and used your RangeMax solution. It works now. I don't know how I missed that extra space but I did. Guess I've been looking at it too long. Thank you very much. I'll mark your answer as correct and give an assist to everyone else...............and talk to the owner's of the spreadsheet this data is loaded from about getting rid of that extra space.