Conditioning a set analysis

[jjking58]

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)

Thanks in advance

[sunny_talwar]

May be like this:

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

[marcus_sommer]

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

[jjking58]

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

[sunny_talwar]

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

[marcus_sommer · Accepted solution]

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

[jjking58]

That will work, Thanks for the quick response

[jjking58]

Thanks for the quick response