Filter the Negative Values from a Calculated Expression

[Anonymous]

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.

[MarcoWedel]

‌please post sample data/app.

thanks

regards

Marco

[Anonymous]

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.

[sinanozdemir]

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.

[Anonymous]

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.

[MarcoWedel]

Hi,

one solution could be:

hope this helps

regards

Marco

[MarcoWedel]

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

hope this helps

regards

Marco

[Anonymous]

Marco,

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

[MarcoWedel · Accepted solution]

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

[sinanozdemir]

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