topic Re: Filter the Negative Values from a Calculated Expression in QlikView

Filter the Negative Values from a Calculated Expression

Anonymous — Wed, 15 Jul 2015 19:02:33 GMT

Hello!

I have a calculated expression =sum(Qty)/Sum( Total<Material,FQr> Qty) ...How can I filter to show only the negative values if my calculated expression is less than the dimension field "Target".... If(sum(Qty)/Sum( Total<Material,FQr> Qty) < Target)

Hope someone can help me on this.

Tks.

Re: Filter the Negative Values from a Calculated Expression

MarcoWedel — Wed, 15 Jul 2015 20:21:41 GMT

‌please post sample data/app.

thanks

regards

Marco

Re: Filter the Negative Values from a Calculated Expression

Anonymous — Wed, 15 Jul 2015 21:15:18 GMT

Hi! Marco,

Thank you for getting back to me quick...Anyway, pls. see my sample table below

Original table ...

Material	C Split	N Split	Shipments (Qty)	Target (Qty)	% Share
A-1245	1	1	1,055	1,055	100.0%
A-1246	1	1	523	523	100.0%
A-1247	1	1	1,325	1,325	100.0%
A-1248	0.2	0.2	720	1,563	9.2%
A-1248	0.8	0.8	7,095	6,252	90.8%
A-1249	0.5	0.5	2,300	3,809	30.2%
A-1249	0.5	0.5	5,318	3,809	69.8%
A-1253	1	1	4,045	4,045	100.0%
A-1254	1	1	17,925	17,925	100.0%
A-1255	0.2	0.2	2,000	800	50.0%
A-1255	0.8	0.8	2,000	3,200	50.0%
A-1256	0.5	1	720	360	100.0%
A-1257	0.5	0.5	50	375	6.7%
A-1257	0.5	0.5	700	375	93.3%
A-1258	0.3	0.3	300	180	50.0%
A-1258	0.7	0.7	300	420	50.0%v

Now, I want to create a new table where I need to filter to pick up the materials with red cells where % share is more than the [C Split] ...Thus, this is my desired table . Once I got my desired output, I would then create a new table for % share with less than the [C split] (this time it's the yellow cells).

Material	C Split	N Split	Shipments (Qty)	Target (Qty)	% Share
A-1248	0.2	0.2	720	1,563	9.2%
A-1248	0.8	0.8	7,095	6,252	90.8%
A-1249	0.5	0.5	2,300	3,809	30.2%
A-1249	0.5	0.5	5,318	3,809	69.8%
A-1255	0.2	0.2	2,000	800	50.0%
A-1255	0.8	0.8	2,000	3,200	50.0%
A-1256	0.5	1	720	360	100.0%
A-1257	0.5	0.5	50	375	6.7%
A-1257	0.5	0.5	700	375	93.3%

I hope I explain this better.

Tks.

Re: Filter the Negative Values from a Calculated Expression

sinanozdemir — Wed, 15 Jul 2015 21:59:03 GMT

Is this what you are looking for?

The first table's expression:

The second table's expression:

Sorry, I cannot attach an app. At work, the firewall is quite strong.

Re: Filter the Negative Values from a Calculated Expression

Anonymous — Wed, 15 Jul 2015 22:33:10 GMT

Hi! Sinan,

The formula worked but how did you suppress the null values? I tried checking the suppress to null values under the dimension and presentation tab to no avail.

Tks.

Re: Filter the Negative Values from a Calculated Expression

MarcoWedel — Wed, 15 Jul 2015 22:33:19 GMT

Hi,

one solution could be:

hope this helps

regards

Marco

Re: Filter the Negative Values from a Calculated Expression

MarcoWedel — Wed, 15 Jul 2015 22:36:07 GMT

I think the difference might be that not only your column "% Share" is an expression.

hope this helps

regards

Marco

Re: Filter the Negative Values from a Calculated Expression

Anonymous — Wed, 15 Jul 2015 22:51:45 GMT

Marco,

Yes, I actually have 5 others calculated expressions...will this matter?

Re: Filter the Negative Values from a Calculated Expression

MarcoWedel — Wed, 15 Jul 2015 23:04:16 GMT

If one of the other expressions is not null, the property "suppress missing" for the "% Share" expression will not supress the row. So one possibility could be to add a condition to all of your expressions (see my previous example).

regards

Marco

Re: Filter the Negative Values from a Calculated Expression

sinanozdemir — Wed, 15 Jul 2015 23:16:01 GMT

Sorry for the late reply.

I agree with MarcoWedel‌

You may want to check other expression and make sure that they are all set to suppress null values

Re: Filter the Negative Values from a Calculated Expression

Anonymous — Wed, 15 Jul 2015 23:55:30 GMT

Hi! Marco,

Thank you so much...it finally worked.

Tks.

Re: Filter the Negative Values from a Calculated Expression

Anonymous — Wed, 15 Jul 2015 23:57:21 GMT

HI! I actually want to flag as "answered"...but there is only helpful in the action drop down...

Rgds...

Re: Filter the Negative Values from a Calculated Expression

MarcoWedel — Thu, 16 Jul 2015 05:19:02 GMT

Try in the thread itself, not in the in-box.

Regards

Marco