NetSuite Workbooks Data Conversions for Hours and Dates

This article is relevant if you are using NetSuite’s new SuiteAnalytics Workbook and you need to act on time entries information with both hours and date formats.

Background

With NetSuite release 2019.1, we have the arrival of the SuiteAnalystics Workbook holding promise to extend query and reporting capacities in NetSuite. I suspect that there will be many enhancements as we go — but it is great to see it in general release so that we can begin to trust that anything we build will be supported.

I run a NetSuite Systems Integration practice with professionals and the timesheet is the major recording instrument used for all of the client value we create. I wanted to explore the new NetSuite pivots feature to see if I can now answer questions that were previously more demanding. In doing so, there were a couple of data conversion formats that I thought the community would be interested in knowing.

Converting NetSuite {hours} to Decimal Format

The timesheet supports an {hours} field which returns a half hour in the format of ‘:30’. I need to work with it to drive more math. How do you get that value to become .5?

Create a formula field of type “FLOAT” and use the following formula:

TO_NUMBER(TO_CHAR({hours}))

Click image for an example.

Calculating NetSuite Timesheet Fees based on {hours} and {rate}

Going further with the {hours} example, now we want to multiply that hourly decimal format by the {rate}. How do you do it?

Create a formula field of type “FLOAT” and use the following formula:

TO_NUMBER(TO_NUMBER(TO_CHAR({hours}))*{rate})

Click image for an example.

Calculating the NetSuite Date Parts to Other String Formats

I wanted to take the {trandate} and have an output that looked like ’01-Jan’, ’02-Feb’ so I could pivot by month and order the output from left to right to produce a natural monthly output. How do you do it?

Create a formula field of type “STRING” and use the following formula:

TO_CHAR({trandate}, 'MM') || '-' || TO_CHAR({trandate}, 'MON')

Click image for an example.

Seeing NetSuite Pivots using Calculated Values

With these three calculated values, I was able to create a NetSuite timesheet-based pivot that illustrated the revenue earned by client, by month. Click image for the result set.

If you are looking to get more out of your NetSuite investment and need expert assistance, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube