Consolidation Function

Consolidation function

Aspose.Cells for Node.js via C++ can be used to apply ConsolidationFunction to data fields (or value fields) of the pivot table. In Microsoft Excel, you can right-click the value field and then select Value Field Settings… option and then select the tab Summarize Values By. From there, you can select any ConsolidationFunction of your choice like Sum, Count, Average, Max, Min, Product, Distinct Count, etc.

Aspose.Cells for Node.js via C++ provides ConsolidationFunction enumeration to support the following consolidation functions.

• ConsolidationFunction.Average
• ConsolidationFunction.Count
• ConsolidationFunction.CountNums
• ConsolidationFunction.DistinctCount
• ConsolidationFunction.Max
• ConsolidationFunction.Min
• ConsolidationFunction.Product
• ConsolidationFunction.StdDev
• ConsolidationFunction.StdDevp
• ConsolidationFunction.Sum
• ConsolidationFunction.Var
• ConsolidationFunction.Varp

How to Apply ConsolidationFunction to Data Fields of Pivot Table Using Aspose.Cells for Node.js via C++

The following code applies Average consolidation function to the first data field (or value field) and DistinctCount consolidation function to the second data field (or value field).

const AsposeCells = require("aspose.cells.node");

//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
//The path to the documents directory.
var dataDir = RunExamples.GetDataDir(".")
//Create workbook from source excel file
var workbook = new AsposeCells.Workbook(dataDir + "Book.xlsx");
//Access the first worksheet of the workbook
var worksheet = workbook.getWorksheets().get(0);
//Access the first pivot table of the worksheet
var pivotTable = worksheet.getPivotTables().get(0);
//Apply Average consolidation function to first data field
pivotTable.getDataFields().get(0).setFunction(AsposeCells.ConsolidationFunction.Average);
//Apply DistinctCount consolidation function to second data field
pivotTable.getDataFields().get(1).setFunction(AsposeCells.ConsolidationFunction.DistinctCount);

//Calculate the data to make changes affect
pivotTable.calculateData();
//Saving the Excel file
workbook.save(dataDir + "output.xlsx");

DISTINCT_COUNT consolidation function is supported by Microsoft Excel 2013 only.