if Statement based on result from an If statement

[davyqliks]

Hi Experts,

I call on your valuable assistance again.

I have the below if Statement giving me a result per line

=Aggr(
If(
Sum(
{<[We Care Materials] = {'Sustainable'}>}
distinct [Garment average net weight (gram)]) * Sum(Distinct [Weight Ratio]) /100 * Sum(Distinct Percentage) / 100 / Sum(Distinct [Garment average net weight (gram)]) * 100
>=50, 'Sustainable','Not Sustainable'),
[Portal Order No.],Fibre,Percentage)

I now need to make a single return per [Portal Order no.]

So if one order is Sustainable and Not sustainable, on a single line i need to specify as Sustainable.

I tried to Add and If statement around the above like:

If(

(ALL OF THE ABOVE)

=>50 and =<50, Sustainable

)

This didnt work, so does anyone have any idea how i can write this expression?

Thank you so much in advance ?

Regards,

Daniel

[Digvijay_Singh]

this looks very complex, quite tough to suggest something without having app to try  few things, may be something like this - 

=Aggr(count(disintct If(

Aggr(

Sum(
{<[We Care Materials] = {'Sustainable'}>}
distinct [Garment average net weight (gram)]) * Sum(Distinct [Weight Ratio]) /100 * Sum(Distinct Percentage) / 100 / Sum(Distinct [Garment average net weight (gram)]) * 100,[Portal Order No.],Fibre,Percentage)
>=50, 'Sustainable','Not Sustainable')=2,'Sustainable'), [Portal Order no.])

[Brysonds]

Can you split the IF statement into two parts so that it processes the sustainable items first?

Sum(Aggr(If({<[We Care Materials] = {'Sustainable'}>} distinct [Garment average net weight (gram)]) * Sum(Distinct [Weight Ratio]) /100 * Sum(Distinct Percentage) / 100 / Sum(Distinct [Garment average net weight (gram)]) * 100 ) >=50, 'Sustainable',
If({<[We Care Materials] = {'Sustainable'}>} distinct [Garment average net weight (gram)]) * Sum(Distinct [Weight Ratio]) /100 * Sum(Distinct Percentage) / 100 / Sum(Distinct [Garment average net weight (gram)]) * 100 ) <50, 'Not Sustainable',[Portal Order No.],Fibre,Percentage))

[davyqliks]

Thank you,

This is a great idea, i added a count first and found that we have many possibilities, maybe Not Sustainable, Not Sustainable and sustainable.

thank you for the suggestion, but this will not work for me.

Kind regards

Daniel