Hi NetSuite Community,
I am working on a Saved Search that displays the total Invoiced Amount, total Booked Orders (Sales Orders) and Gross Profit % per Item Category. For context, Item Category is related to the Items of the Sales Transactions. It is like a Segment.
Here is the sample:
I used Sum to summarize the currency fields and Average to summarize the Percentage fields
However, in the Gross Percentage columns (YTD and MTD), the users want to see a different thing. They want to extract the Sum of the Invoiced Amount per Item Category, then Sum of the COGS amount in the Item Fulfillment to get the Gross Percentage (Invoiced Amount (Sum) - COGS Amount ((Sum))/Invoiced Amount (Sum)
I tried having this kind of Formula but is having an Unexpected error:
Invoiced GP% (YTD):
CASE WHEN {trandate} BETWEEN TO_DATE('11/1/2025', 'mm/dd/yyyy') AND {today} THEN CASE WHEN {type} = 'Sales Order' THEN CASE WHEN {fulfillingtransaction.cogsamount} IS NULL THEN NULL ELSE ((SUM/* /({billingtransaction.amount}) OVER(PARTITION BY INITCAP({item.cseg_f5_secondary_c_2}))-SUM/ /({fulfillingtransaction.cogsamount}) OVER(PARTITION BY INITCAP({item.cseg_f5_secondary_c_2})))/NULLIF(SUM/ */({billingtransaction.amount}) OVER(PARTITION BY INITCAP({item.cseg_f5_secondary_c_2})),0)) END ELSE NULL END ELSE NULL END
Is there anyone here from the Team had any experience of doing anything like this?
In my opinion, this seems like a limitation since the Saved Search only allows few summarizations including Min, Max, Sum, and Average. Just want to get the inputs of the experts here if they have any suggestions or should we go to a more complicated route like scripting it, etc.
Thank you!