Announcements
Meet our latest featured member Bruno! Drop a like and comment. READ ON
cancel
Showing results for
Did you mean:
Contributor II

## Exclude zero (0) in set analysis

Hi,

I have tried everything to exclude a 0 qty in my set analysis but nothing seems to work. this is the last 2 I tried:

1. Sum({<[Purchase Order Line Outstanding Quanity]-={0}>} [Purchase Order Line Outstanding Quanity])
2. Sum([Purchase Order Line Outstanding Quanity]) >0

Labels (1)
• ### Scripting

1 Solution

Accepted Solutions
Creator

I think this expression works for u

If([Purchase Order Line Outstanding Quanity] <>0,  Sum([Purchase Order Line Outstanding Quanity]))

Please like and accept the solution if you liked it.

15 Replies
Master

Try something like this.

Sum({<Dim={"=Sum({1} [Purchase Order Line Outstanding Quanity])>0"}>} [Purchase Order Line Outstanding Quanity])

Contributor II
Author

Hi there,

Still no luck - I've also removed all the additional fields and removed the tick "include null" on all the dimensions.

Master

What if as a calculated dimension, and untick Show Null Values?

= Aggr(If(Sum( [Purchase Order Line Outstanding Quanity])>0, Dim), Dim)

Contributor II
Author

Hello, please excuse my lack of knowledge. This is my first time requesting assistance. Would you prefer screenshots or the actual application?

Master

Sure thing.

Creator

I think this expression works for u

If([Purchase Order Line Outstanding Quanity] <>0,  Sum([Purchase Order Line Outstanding Quanity]))

Please like and accept the solution if you liked it.

Contributor II
Author

Thank you, but it seems part of the IF statement is missing, what happens if it is true?
I have tried as is, but then it returns all lines, just with a dash - instead of a 0

Contributor II
Author

Sorry forgot to add the screen shot:

Contributor II
Author

Hi,
I've tried this again, same result, I get a dash "-"; but it doesn't exclude the line items for 0 qty's.

Community Browser