NetSuite: Saved Search to Return One of Two Aggregated Values

Let's begin with a simple query. In an opportunity search, one field aggregates Estimated Gross Profit of each line item, while another aggregates the Actual Profit of each line item. Let's label each field {custcol1} and {custcol2}. We set the summary types to Sum. Simple.

Now we'd want a Formula (Currency) that returns {custcol2}, but if that has 0 or null values, we return {custcol1}. So...

case when nvl({custcol2},0) = 0 then nvl({custcol1},0) else nvl({custcol2},0)) end

...would work, but would likely cause duplicates. Using max on each variable could be a solution to that, but then again, there could be some opportunities with multiple line items. Using max would only get the biggest of those line items, missing out on the smaller lines. A better solution would be using sum distinct instead:

case when sum(distinct nvl({custcol2},0)) = 0 then sum(distinct nvl({custcol1},0)) else sum(distinct nvl({custcol2},0)) end

In most cases, this would work. But there would be extremely rare occasions when an opportunity would contain different items, but each line item would have the same Profit values. Sum distinct would not differentiate between such lines. But luckily, we can still get around this. By using another field as a way to make a value distinct, then aggregating the result, and taking what we've learned previously, we can come up with interesting results.

to_number(case when sum(distinct nvl({custcol2},0)) = 0 then listagg(distinct regexp_substr(nvl({custcol1},0) || ' ' || {item},'(\S*)',1,1),'+') else listagg(distinct regexp_substr(nvl({custcol2},0) || ' ' || {item},'(\S*)',1,1),'+') end)

Instead of using sum distinct, we would use listagg distinct, and set the delimiter to +. Apparently listagg considers + delimiters as an actual mathematical operator. Cool. So basically what this formula does is that it concatenates a value and item together, and puts a space in between, for instance, if a banana costs 10 dollars, and an apple costs 12 dollars, the listagg function would return something like this: "10 Banana+12 Apple". The arguments of the regexp_substr only get everything before the first space for each item, so this essentially returns 10+12. Then listagg adds whatever's left, since it treats + delimiters as mathematical operators if it can (results are number-like). The aggregated result is treated as a string because of listagg, but can be converted into a number with the use of to_number. We need the result to be a number so it can be evaluated as a currency.

So what did we learn? Listagg SOMETIMES treats + delimiters as mathematical operators if it can.