Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Netsuite Search Formula for calculating Net Promoter Score

WaalkerMar 15 2018 — edited Mar 15 2018

Hello all, I would like to say I have a very low knowledge of SQL etc. Enough to get by and do some expression writing etc, but easily stumped on logic/syntax. The first thing I have posted below is a WORKING formula to calculate the NPS in our system, it does use a Date Criteria in Netsuite and is calculating for a month.

SUM(Case When {custrecord_rsm_csf_rec} = 'Very Likely' Then 1 Else 0 End -

Case When {custrecord_rsm_csf_rec} = 'Neither Likely or Unlikely' Then 1

When {custrecord_rsm_csf_rec} = 'Unlikely' then 1

When {custrecord_rsm_csf_rec} = 'Very Unlikely' then 1 Else 0 End)

/count({internalid})

What I am trying to do now is add some to it that will calculate this monthly and display that as a 12 month visual add when you run the search. I have tried many variations with this, got from receiving total errors to getting data returned, but I know the number is wrong. What I am trying to add to this is the to_char({created{, 'MM') = 03 which I know is grouping it by month. When I have that working successfully I can break that out into 12 separate formulas, 1 for each month.

SUM(Case When {custrecord_rsm_csf_rec} = 'Very Likely' Then (case when to_char({created}, 'MM') = 03) 1 Else 0 End -

Case When {custrecord_rsm_csf_rec} = 'Neither Likely or Unlikely' Then (case when to_char({created}, 'MM') = 03) 1

When {custrecord_rsm_csf_rec} = 'Unlikely' then 1

When {custrecord_rsm_csf_rec} = 'Very Unlikely' then 1 Else 0 End)

/count({internalid})

Any guidance would be much appreciated.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 12 2018
Added on Mar 15 2018
3 comments
326 views