topic Re: Exclude zero (0) in set analysis in QlikView

Exclude zero (0) in set analysis

Dianne — Fri, 28 Apr 2023 07:47:19 GMT

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

Re: Exclude zero (0) in set analysis

BrunPierre — Fri, 28 Apr 2023 08:29:05 GMT

Try something like this.

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

Re: Exclude zero (0) in set analysis

Dianne — Fri, 28 Apr 2023 08:33:46 GMT

Hi there,

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

Re: Exclude zero (0) in set analysis

BrunPierre — Fri, 28 Apr 2023 08:50:32 GMT

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

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

Re: Exclude zero (0) in set analysis

Dianne — Fri, 28 Apr 2023 08:53:56 GMT

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

Re: Exclude zero (0) in set analysis

BrunPierre — Fri, 28 Apr 2023 09:07:25 GMT

Sure thing.

Re: Exclude zero (0) in set analysis

Prem0212 — Fri, 28 Apr 2023 08:59:23 GMT

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.

Re: Exclude zero (0) in set analysis

Dianne — Wed, 03 May 2023 11:10:41 GMT

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

Re: Exclude zero (0) in set analysis

Dianne — Wed, 03 May 2023 11:11:46 GMT

Sorry forgot to add the screen shot:

Re: Exclude zero (0) in set analysis

Dianne — Wed, 03 May 2023 11:15:02 GMT

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

Re: Exclude zero (0) in set analysis

Dianne — Wed, 03 May 2023 11:16:44 GMT

Hi,
This solution gives 0,00 as the result instead of excluding the lines.

Re: Exclude zero (0) in set analysis

Or — Wed, 03 May 2023 11:55:29 GMT

I'm a tad confused here.

Sum({<[Purchase Order Line Outstanding Quanity]-={0}>} [Purchase Order Line Outstanding Quanity]) should be the exact same thing as:

Sum([Purchase Order Line Outstanding Quanity])

Since if this field is 0 or null, it doesn't change the sum in any way.

This doesn't mean you can't come up with a result that's 0, if there are no associated non-zero quanity[sic] or if the values add up to 0 (e.g. 4, -3, -1).

Hiding 0 rows will hide rows where all measures are 0/null, not this specific one. If you want to hide rows where this specific field is null, you can either try using dimension limits (which would require placing this measure in front of the other measures) or repeat the appropriate set for all measures in this object to ensure they wind up as 0 when appropriate.

Re: Exclude zero (0) in set analysis

Kushal_Chawda — Wed, 03 May 2023 12:51:57 GMT

@Dianne It seems you have many other measures in your table which is why you are not able to exclude the quantity from table. You need to apply quantity>0 condition in all your measures then only it will work

For Quantity Measure =if(Sum([Purchase Order Line Outstanding Quanity]) >0,Sum([Purchase Order Line Outstanding Quanity]) ,0)

For Unit Price Measure =if(Sum([Purchase Order Line Outstanding Quanity]) >0,Sum([Unit Price]) ,0)

likewise apply condition for all measures.

Then go to Add-Ons, and uncheck 'Include zero values' option

Re: Exclude zero (0) in set analysis

Dianne — Wed, 03 May 2023 13:04:08 GMT

Hi,

Thank you! will try that

Re: Exclude zero (0) in set analysis

Diego_780 — Wed, 03 May 2023 14:22:19 GMT

It seems that you don´t want to show the line in Dimension when it´s sum is 0, if you use that expression: Aggr(If(Sum( [Purchase Order Line Outstanding Quanity])>0, Dimension), Dimension), you should put it as a Dimension, not as a measure.

Re: Exclude zero (0) in set analysis

Prem0212 — Thu, 04 May 2023 05:37:05 GMT

I think this expression will exclude the zero values Sum({<[Purchase Order Line Outstanding Quanity] = -{0} >} [Purchase Order Line Outstanding Quanity]).