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.