Part 2: Intermediate Techniques in NetSuite Analytics – Leveraging Calculated Fields, Custom Metrics, and Formulas

Introduction

Welcome back to our three-part series on NetSuite Analytics Workbooks! In Part 1 of this series, we introduced the basics of Analytics Workbooks and how they enable you to create interactive reports and visualizations. Now that you’re familiar with the foundational features, it’s time to dive into intermediate techniques that make Analytics Workbooks a powerful tool for more detailed analysis. In this post, we’ll cover creating calculated fields, custom metrics, and dataset formulas that help you gain deeper insights into your business data. We’ll walk through practical examples like Gross Margin and Aging calculations—essential metrics for managing profitability and accounts receivable effectively.

Understanding Calculated Fields vs. Dataset Formulas

Before diving into specific examples, it’s important to understand the difference between calculated fields and dataset formulas in NetSuite Analytics Workbooks:

1. Calculated Fields: Calculated fields are created directly within a workbook and apply only to that specific workbook’s report. They are ideal for one-off metrics or analyses that don’t need to be reused in other workbooks.
2. Dataset Formulas: Created at the dataset level, dataset formulas are available across all workbooks that use the dataset. They’re best suited for commonly used metrics like Gross Margin Percent or Aging, which can be reused in multiple reports, ensuring consistency.

Understanding the difference helps you decide which approach best meets your reporting needs while keeping your workbooks organized and optimized for multiple applications.

Example Calculations: Gross Margin Dollars and Gross Margin Percent

Gross Margin is a critical metric in financial reporting that reveals how much profit is left after covering the cost of goods sold (COGS). We’ll calculate Gross Margin in two ways:

• Gross Margin Dollars: Shows the dollar difference between revenue and cost.
• Gross Margin Percent: Shows the percentage of revenue that remains after accounting for COGS.

Let’s break down how to set up these calculated fields in NetSuite Analytics Workbooks.

1. Gross Margin Dollars
Gross Margin Dollars is the difference between revenue and cost, giving you the absolute dollar value of gross margin.

• Formula: {Revenue} – {Cost}
• Steps:
  • In the workbook, go to Fields > Add Calculated Field.
  • Name the field Gross Margin Dollars.
  • Enter the formula {Revenue} – {Cost}, where {Revenue} represents your sales or income field and {Cost} is your cost field.
  • Click Apply to add this calculated field to your workbook, displaying the Gross Margin dollar amount.

This field provides a quick view of profitability at the transactional or product level.

2. Gross Margin Percent
Gross Margin Percent provides a view of profitability as a percentage, which makes it easier to compare across different categories or periods.

• Formula: ({Revenue} – {Cost}) / {Revenue} * 100
• Steps:
  • To create Gross Margin Percent, add a calculated field in the workbook following the same steps as above.
  • Name the field Gross Margin Percent.
  • Enter the formula ({Revenue} – {Cost}) / {Revenue}.
  • Apply percentage formatting to enhance readability.

Gross Margin Percent gives a clear view of profitability as a ratio, helping you make data-driven decisions on pricing, product lines, and customer targeting.

Creating an Aging Formula for Accounts Receivable

Aging calculations are essential for tracking the timeliness of invoice payments and managing accounts receivable. To calculate the age of invoices, use the following formula:

Formula: ROUND(CAST_TO_DATE(CURRENT_DATE) – {trandate})

This formula calculates the days between the invoice date ({trandate}) and today’s date, rounded to the nearest whole number.

Steps to Create the Aging Formula:

1. In the dataset, go to the formula section to create a new field for Aging. This approach makes the aging calculation available for reuse across multiple workbooks.
2. Name the formula Days Outstanding.
3. Enter the formula ROUND(CAST_TO_DATE(CURRENT_DATE) – {trandate}).
4. Apply the formula to display the number of days since each transaction’s date.

Tip: Use conditional formatting to highlight overdue invoices (e.g., invoices over 30 days) to help prioritize follow-ups. This Days Outstanding field gives your team a clear view of aging receivables, enabling them to manage collections more effectively.

Using Pivot Tables for Deeper Analysis

Once you have calculated fields like Gross Margin and Days Outstanding, leverage pivot tables to summarize and analyze these metrics. Pivot tables enable you to aggregate data easily drawing valuable insights without needing additional reports.

Examples of Pivot Table Applications:

• Top Products by Gross Margin: Create a pivot table to display your top products based on Gross Margin Dollars. This view highlights which products are contributing most to profitability.

• Aged Receivables Summary: Use the Days Outstanding field to categorize invoices by age (e.g., 0-30 days, 31-60 days, 61-90 days). This summary helps identify overdue accounts and prioritize collections.

Pivot tables allow you to analyze data more efficiently, providing a deeper understanding of your financials and helping to guide business decisions.

Best Practices for Using Calculated Fields and Formulas in Workbooks

To maximize the effectiveness of calculated fields and dataset formulas, keep these best practices in mind:

1. Use Calculated Fields for Specific Reports: For one-off or report-specific calculations, add calculated fields directly within the workbook.
2. Create Dataset Formulas for Reusability: For commonly used metrics like Gross Margin Percent or Days Outstanding, create formulas at the dataset level. This way, you can reuse them across multiple reports, ensuring consistency.
3. Apply Conditional Formatting: Use formatting to highlight key metrics, such as overdue invoices or negative gross margins, making it easier to spot issues quickly.
4. Combine Metrics in Pivot Tables: Calculated fields are even more powerful when combined in pivot tables. Group, filter, and summarize data to uncover deeper insights efficiently.

Conclusion

With calculated fields, custom metrics, and dataset formulas, Analytics Workbooks in NetSuite offers a flexible, powerful way to transform raw data into actionable insights. When creating custom metrics like Gross Margin and Days Outstanding, you can monitor profitability, manage receivables, and make data-driven decisions more effectively. In Part 3, we’ll explore dashboard creation and advanced

About Us

We are a NetSuite Solutions Partner and reseller with 30+ years of combined experience.  We specialize in implementation, optimization, integration, rapid project recovery and rescue as well as custom development to meet any business need. If you would like more information on NetSuite or are in need of consultation for your project or implementation, feel free to contact NetSuite support.

To Contact Us Click Here