Hello everyone!
I hope you are doing well!
Context
I am working on a NetSuite Analytics dataset related to revenue recognition. The dataset includes a field called Posting Period, which stores values like "Dez 2024" (December 2024) as text. Since these values are stored as text, sorting them does not follow the correct chronological order.
To properly sort and filter data by date, I need to convert this text-based period into a DATE type field.
Challenges
- The
Posting Period values are formatted as "MMM YYYY" (e.g., "Jan 2024", "Fev 2025", "Nov 2023", etc.). - Sorting as text leads to incorrect ordering (
Abr 2024 might appear before Jan 2024). - The formula needs to be optimized for performance, avoiding heavy operations like multiple
CASE conditions or excessive string manipulations.
The current formula is returning an error in the NetSuite backend:
Error message:
"A non-numeric character was found where a numeric was expected"
This issue was identified with NetSuite Support.
Current Formula
To convert "MMM YYYY" into a valid DATE (YYYY-MM-DD), I built the following optimized formula using TO_DATE():
TO_DATE(
CASE
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Jan%' THEN '01'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Fev%' THEN '02'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Mar%' THEN '03'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Abr%' THEN '04'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Mai%' THEN '05'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Jun%' THEN '06'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Jul%' THEN '07'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Ago%' THEN '08'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Set%' THEN '09'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Out%' THEN '10'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Nov%' THEN '11'
WHEN COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}) LIKE '%Dez%' THEN '12'
ELSE NULL
END
|| '/01/' ||
SUBSTR(COALESCE({plannedrevenue.postingperiod#display}, {plannedrevenue.plannedperiod#display}), -4, 4),
'MM/DD/YYYY'
)
Expected Output
Input (Posting Period) | Converted Date (TO_DATE) |
|---|
"Dez 2024"
| 2024-12-01
|
"Jul 2025"
| 2025-07-01
|
"Mar 2023"
| 2023-03-01
|
- What is the best practice for structuring these date-related fields in NetSuite to ensure they are stored and sorted correctly?
- Is there an adjustment to this formula that can prevent the "non-numeric character" error?
- Would a different approach (such as a formula field in NetSuite instead of Analytics) improve performance?
- Are there any known limitations with
TO_DATE() in NetSuite that could cause issues?
- We already have a defect opened with Oracle, but due to urgency, I am also requesting help here.
- Any insights, best practices, or alternative solutions would be greatly appreciated! 🚀