7 Replies Latest reply: Dec 8, 2016 12:11 PM by Jim King

# Conditioning a set analysis

Is there a way to test the result of the following and zero out the result if it comes back negative?

SUM({<IDPAGR={'PART','MECH','OCSE','COOL','CAFE'}>}Gross) / sum({<IDPAGR={'PART','MECH','OCSE','COOL','CAFE'}>}Amount)

• ###### Re: Conditioning a set analysis

May be like this:

RangeMax(SUM({<IDPAGR={'PART','MECH','OCSE','COOL','CAFE'}>}Gross) / sum({<IDPAGR={'PART','MECH','OCSE','COOL','CAFE'}>}Amount), 0)

• ###### Re: Conditioning a set analysis

It's not quite clear for me which cases you want to exclude with "negative"?

Maybe with something like this: rangemin(YourExpression, 0). If the aim is to prevent a division through 0 you could use something like:

SUM({<IDPAGR={'PART','MECH','OCSE','COOL','CAFE'}>}Gross) / alt(sum({<IDPAGR={'PART','MECH','OCSE','COOL','CAFE'}>}Amount),1)

- Marcus

• ###### Re: Conditioning a set analysis

Neither solution seems to work.The problem seems to be when the gross profit % is calculated over multiple products and the result is highlighted below. Is there any way of zeroing this result out.

Thanks again for you help

• ###### Re: Conditioning a set analysis

You want to 0 out the number you have highlighted in Yellow? What about -32.7%?

• ###### Re: Conditioning a set analysis

That will work, Thanks for the quick response

• ###### Re: Conditioning a set analysis

Your highlighted value isn't negativ only quite small with: 0,000000000000342 and it's just an exponential format. This meant you could simply add respectively adjust the format - either within the properties in the tab numbers or per function like: num(Expression, '#,##0.0').

- Marcus

• ###### Re: Conditioning a set analysis

Thanks for the quick response