This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Struggling a lot with trying to get a custom KPI search for "average lines per (sales) order"
Tried to resort to using the PLSQL trick, but then I can't apply the "Average" summary

If the transaction main line knew how many lines it contained, it would be a piece of cake

Can you post the PLSQL you tried? Was it something like:

`COUNT({line}) / COUNT(DISTINCT {internalid})`

Rather:

COUNT({line}) / COUNT(DISTINCT {tranid})

Probably need some logic in the COUNT() to also detect mainline or not.

Yeah, similar stuff, using the comment trick to execute the analytic function:
`COUNT/*SQL*/(*) OVER(PARTITION BY {postingperiod}) / COUNT/*SQL*/(DISTINCT {internalid}) OVER(PARTITION BY {postingperiod})`
And I have a way of detecting the first line (rather than mainline):
`CASE WHEN LAG/*SQL*/({internalid}) OVER(ORDER BY {internalid}) != {internalid} THEN` [first line]
However the big gotcha is that I can't apply the "Average" summary function to this SQL code, and therefore can't make it into a custom KPI search:
<https://netsuite.custhelp.com/app/answers/detail/a_id/8104>

Are you grouping by posting period? If so, then you wouldn't need to try the partition method.