When we onboard NetSuite customers to iCharts, we often find data structures that have been optimized for day-to-day operations. This can be a problem because what works for everyday use won’t necessarily lead to easy analysis and visualization in the future.

For example, let’s say you’re a mid-sized manufacturer of athletic gear that runs its operations in NetSuite. Any member of your finance or manufacturing team could easily tell you the specific product line that any one of your products belongs to, but the data was captured in this format:

Category Item Name Description
Headwear Scrunch Hat Black, Medium, blue horizontal stripes, cotton stretch, tiger graphic
Apparel Air Shirt White, perforated neoprene mesh blend, red collar, diagonal stylized
Shoes Jordan Jr’s Red/blue, Leather with stretch mesh, pump tongue, air bubble sole, jordan thumb graphic
Shorts Air Shorts Black, perforated neoprene mesh blend, red vertical stripe, diagonal stylized

The description field in the data structure above may work perfectly well for running the organization, but   it’s not ideal for deeper analysis. Data analysis and visualization requires a structure like the one shown below–and getting from the original structure to an ideal structure will require you to pull off a few tricks.

Category Item Name Size Color Accent Color Line Graphic Material
Headwear Scrunch Hat Medium Black Blue Champ Tiger Cotton Stretch
Apparel Air Shirt Large White Red Ninja Diagonal Stylized Perforated Neoprene
Shoes Jordan Jr’s 9.5 Red Blue Jordans Jordan Thumb Leather
Shorts Air Shorts Large Black Red Ninja Diagonal Stylized Perforated Neoprene

CASE WHEN Statements

We often recommend using NetSuite’s CASE WHEN Statements to parse out the description field. You can use these statements when you’re faced with a data structure that’s making it tough to analyze or visualize your data.

CASE WHEN Statements are very much like IF statements from Excel. In NetSuite, they can be easily adapted to get custom results from your data.

You can use statements like the CASE WHEN anywhere within NetSuite that you can place a formula. Most often, this will be as a result in a saved search or in a custom field driven by a formula, also known as a custom formula field.

An important point to remember: When you use a formula, that means your data isn’t as accessible as it should be in its current instance.  A formula will always take time to implement, every time it’s  needed. To save time and make your data more accessible, consider  making a custom field for any formula you find yourself using more than once.

Here’s a peek at how you can use CASE WHEN statements:

Create groups for more specific or more general filtering options

NetSuite has fields for individual locations and the country for those locations, but it would be useful to have a medium level that groups locations into regions or areas. For example, if we wanted to group our five locations in California, Oregon and Washington into a region called “Western U.S.,” the CASE WHEN statement could look like this:

Using a Formula(Text):

CASE WHEN {location} IN (’San Francisco’,’Los Angeles’,’Portland’,’Seattle’,’Yakima’) THEN ‘Western US’ ELSE {location} END

This formula would produce a column in your saved search results that states “Western U.S.” for any record belonging to the five locations above. For other records, it would state the specific location.

A casual summary of this formula would be:

“In the case that the location of a record is in San Francisco, Los Angeles, Portland, Seattle or Yakima then put it into a group called ‘Western U.S.’ Otherwise, if the location is outside of those five, give me the specific name of that location.”

Calculate a custom metric for Net Income (Income – Costs)

It may be valuable at a certain point to produce values that are consistent with your Income Statement or Balance Sheet. You can do this using CASE WHEN Statements. A CASE WHEN statement to retrieve your Net Profit could look something like this:

CASE WHEN {accounttype} = ‘Income’ THEN {amount} ELSE 0 END

CASE WHEN {accounttype} IN (‘Expense’,’Cost of Goods Sold’) THEN {amount} ELSE 0 END

To break this down, I am actually using two CASE WHEN statements.

The top statement:

CASE WHEN {accounttype} = ‘Income’ THEN {amount} ELSE 0 END

This isolates any values that are from accounts that fall under Income.

The bottom statement:

CASE WHEN {accounttype} IN (‘Expense’,’Cost of Goods Sold’) THEN {amount} ELSE 0 END

This combines any values that are from accounts that fall under Expense and Cost of Goods.

The minus (-) in between effectively subtracts all Expense and Cost of Goods amounts from Income, giving you your Net Income.

Each company implements and names its accounts and account types in a custom way, so this is not a one-size-fits-all solution. However, you can follow this format to isolate elements like Income, Expenses, and Cost of Goods in any way that you wish to calculate them with one another.

To learn more about best practices for data analysis and visualization with NetSuite, sign up for our monthly webinar or try our Interactive Demo below:

Try the iCharts Interactive Demo