Sum - chart function

Overview

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Customer

  • Product

  • UnitSales

  • UnitPrice

Load script

Example:
LOAD * inline [
Customer|Product|UnitSales|UnitPrice
Astrida|AA|4|16
Astrida|AA|10|15
Astrida|BB|9|9
Betacab|BB|5|10
Betacab|CC|2|20
Betacab|DD||25
Canutility|AA|8|15
Canutility|CC||19
] (delimiter is '|');

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• Customer

• Product

Create the following measures:

• =Sum(UnitSales), to calculate the sum of UnitSales.

• =Sum(UnitSales*UnitPrice), to calculate the aggregated total of UnitSales multiplied by UnitPrice.

• =Sum(TOTAL UnitSales*UnitPrice), to calculate the total for all rows in the table as well as the total, because the TOTAL qualifier means the sum is the same for all cells, disregarding the chart dimensions.

• =Sum({1} TOTAL UnitSales*UnitPrice), to calculate the total, independent of the selection made, because the set analysis expression {1} defines the set of records to be evaluated as ALL, no matter what selection is made.

In this example, observe the following:

• For the first measure, Sum(UnitSales, the Totals cell returns 38, which is the total of the values in UnitSales across all customers and products. The results for each customer and product combination are aggregated to return the total sum of UnitSales for each combination. For example, the sum of all UnitSales of product AA from customer Astrida is 14.

• For the second measure, Sum(UnitSales*UnitPrice), the Totals cell returns 505. This is the aggregated total of UnitSales multiplied by UnitPrice for each customer and product combination.

• For the third measure, Sum(TOTAL UnitSales*UnitPrice), the value returned in all rows including Totals is 505, because the TOTAL qualifier disregards the chart dimensions. However, if a selection is made on a dimension such as Customer, then the values will change. For example, if you select Betacab, the resulting table will have only three rows, one for each of the Betacab records, but the column will return the same total value for all rows (90).

• The fourth measure, Sum({1} TOTAL UnitSales*UnitPrice), returns the value 505 independent of any selections because the set analysis expression {1} defines the set of records to be evaluated as ALL. If you select a customer, for example, Canutility, the value returned is still 505.

Overview

A dataset contains sales figures for stores by region. The company wants to aggregate the sales.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Month

  • SalesProduct

  • Region

  • Sales

Load script

Example:
Load * inline [
Month,SalesProduct, Region, Sales
2024-01-01, T-shirt, East, 100
2024-01-01, T-shirt, West, 120
2024-01-01, Jeans, East, 200
2024-01-01, Jeans, West, 180
2024-02-01, T-shirt, East, 150
2024-02-01, T-shirt, West, 130
2024-02-01, Jeans, East, 250
2024-02-01, Jeans, West, 220
];

Results

Load the data and open a sheet. Create a new pivot table and add these fields as row dimensions:

• Month

• Region

Add this field as a column dimension:

• SalesProduct

Create the following measure:

• =Sum(Sales), to calculate the total sales for each product.

To expand the pivot table to see all rows, in the properties panel, under Appearance, select Presentation. Under Rows, select Fully expanded.

The results show the sales for each SalesProduct by Month and Region, with an aggregated total for each Month.