Share via

Ad-hoc analysis of sales data

  • Article

This article explains how to use the Data Analysis feature to analyse sales data directly from list pages and queries. You don't have to run a report or switch to another application, such as Excel. The feature provides an interactive and versatile way to calculate, summarize, and examine data. Instead of running reports using options and filters, you can add multiple tabs that represent different tasks or views on the data. Some examples are "My customers" or "Sales statistics," or any other view you can imagine. To learn more about how to use the Data Analysis feature, go to Analyse list and query data with analysis mode.

Use the following list pages for ad-hoc analysis of sales processes:

Sales ad-hoc analysis scenarios

Use the Data Analysis feature for quick fact checking and ad-hoc analysis:

  • If you don't want to run a report.
  • If a report for your specific need doesn't exist.
  • If you want to quickly iterate to get a good overview on a part of your business.

The following sections provide examples of sales scenarios in Business Central.

Area To... Open this page in analysis mode Using these fields
Sales (expected sales volume) Analyse your expected sales volume. Sales Orders Sell-to Customer Name, Sell-to Customer No., No. , Amount, Document Date Year, and Document Date Month.
Sales (Customer sales by volume) Get an overview of the customers that purchase the most, or that owe the most. Customer Ledger Entries Customer Name, Document No., Amount, and Remaining Amount.
Finance (Accounts Receivables) See what your customers owe you, for example, broken down into time intervals for when amounts are due. Customer Ledger Entries Customer Name, Due Date, and Remaining Amount.

Example: Sales (expected sales volume)

To analyse your expected sales volume and sales amounts for unshipped orders for each customer by year or month, follow these steps:

  1. Open the Sales Orders list and turn on analysis mode.
  2. Go to the Columns menu and remove all columns (select the box next to the Search field).
  3. Turn on Pivot mode (located directly above the Search field).
  4. Drag the Sell-to Customer Name, Sell-to Customer No., and No. fields to the Row Groups area. Drag the fields in that order.
  5. Drag the field Amount field to the Values area.
  6. Drag the Document Date Year and Document Date Month fields to the Column Labels area. Drag the fields in that order.
  7. To do the analysis for a given year or quarter, apply a filter in the Additional Filters menu. The menu is on the right of the page, just below the Columns menu.
  8. Rename your analysis tab to Expected sales volume or something that describes this analysis for you.

Example: Sales (Customer sales by volume)

To produce an overview of the customers that purchase the most or that owe the most, follow these steps:

  1. Open the Customer Ledger Entries list, and switch on analysis mode.
  2. Go to the Columns menu and remove all columns (select the box next to the Search field).
  3. Drag the Customer Name field to the Row Groups area, and the Document No. field below it.
  4. Choose the Amount and Remaining Amount fields.
  5. To do the analysis for a given year or quarter, apply a filter in the Additional Filters menu. The menu is on the right of the page, just below the Columns menu.
  6. Rename your analysis tab to Customer by sales volume, or something that describes this analysis for you.

The following image shows the result of these steps.

Example of how to do data analysis on the Customer Ledger Entries page.

Example: Finance (Accounts Receivables)

To see what your customers owe you, maybe broken down into time intervals for when amounts are due, follow these steps:

  1. Open the Customer Ledger Entries list and turn on analysis mode.
  2. On the Columns menu, remove all columns (select the box next to the Search field).
  3. Turn on Pivot mode (located directly above the Search field).
  4. Drag the Customer Name field to the Row Groups area, and drag the Remaining Amount field to the Values area.
  5. Drag the Due Date Month field to the Column Labels area.
  6. To do the analysis for a given year or quarter, apply a filter in the Additional Filters menu. The menu is on the right of the page, just below the Columns menu.
  7. Rename your analysis tab to Aged Accounts by Month, or something that describes this analysis for you.

Data foundation for ad-hoc analysis on sales

After you enter information on a sales order and add all the sales order lines, you can post the order. Posting creates a shipment and an invoice. Business Central updates the customer's account, general ledger, and item ledger entries:

  • For each sales order, a sales entry is created on the G/L Entry table. An entry is also created in the customer's account in the Cust. Ledger Entry table and a general ledger entry is created in the relevant receivables account. In addition, posting the order might result in a GST/HST entry and a general ledger entry for the discount amount.
  • For each sales order line, an item ledger entry is created in the Item Ledger Entry table (if the sales lines contain item numbers) or a general ledger entry is created in the G/L Entry table (if the sales lines contain a general ledger account). In addition, sales orders are always recorded in the Sales Shipment Header and Sales Invoice Header tables.

To learn more about posting sales, go to Posting sales.

See also

Posting sales
Analyse list and query data with analysis mode
Sales analytics overview
Analytics, business intelligence, and reporting overview
Sales overview
Work with Business Central

Start a free trial!

Find free e-learning modules for Business Central here