Data Fields and Calculations

This topic explains how to apply multiple calculation functions to the same field, create calculated fields with custom formulas, add calculated items for custom data analysis, and set Show Values As to display data as percentages, differences, or running totals.

Add field function

In order to add field function in a pivot table, refer to the following example code.

// Change or set data field's summarize function.
field_Amount.setFunction(ConsolidationFunction.Average);

Apply Different Calculations on a Pivot Field

In DsExcel, you can add a pivot table field to a pivot table multiple times by applying various calculation functions on it. These functions include sum, average, min, max, count etc. The final pivot table output will contain multiple data fields based on the calculations applied over the pivot table field.

Refer to the following example code to add a pivot table field as multiple data fields by applying different calculation functions.

// Config pivot table's fields
IPivotField field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.RowField);

IPivotField field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.RowField);

// Sum function on Amount field
IPivotField field_Amount = pivottable.getPivotFields().get("Amount");
pivottable.addDataField(field_Amount, "sum amount", ConsolidationFunction.Sum);

// Count function on Amount field
IPivotField field_Amount2 = pivottable.getPivotFields().get("Amount");
pivottable.addDataField(field_Amount2, "count amount", ConsolidationFunction.Count);

The output of above example code when viewed in Excel, looks like below:

Calculated Fields

Calculated fields in pivot table refer to the data fields created by applying additional logic or formula on existing data fields of the underlying data source. These fields are especially useful when summary functions and custom calculations do not generate the desired output. For instance, an employee database of a company holds data about existing salary and performance rating of each employee. At year end, one can easily calculate the raised salary of employees by creating calculated field using salary and the rating field.

In DsExcel, the getCalculatedFields method represents the collection of all calculated fields in a particular pivot table. You can use add method of the ICalculatedFields interface to create a new calculated field in the pivot table. The Add method accepts string parameters of field name and formula to generate the calculated field. To remove a calculated field from the collection you can use the remove method which takes target field name as its parameter.

Refer to the following code to create a calculated field in the pivot table:

IWorksheet calculatedFieldSheet = workbook.getWorksheets().add();
calculatedFieldSheet.setName("CalculatedField");

// Add pivot table.
IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:F71"));
IPivotTable calculatedFieldTable = calculatedFieldSheet.getPivotTables().add(pivotCache, calculatedFieldSheet.getRange("A1"));
calculatedFieldTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
calculatedFieldTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);

// Add calculated field.
calculatedFieldTable.getCalculatedFields().add("Tax", "=IF(Amount > 1000, 3% * Amount, 0)");

// Set calculated field as data field.
calculatedFieldTable.getPivotFields().get("Tax").setOrientation(PivotFieldOrientation.DataField);

calculatedFieldTable.getDataFields().get("Sum of Amount").setNumberFormat("$#,##0_);($#,##0)");
calculatedFieldTable.getDataFields().get("Sum of Tax").setNumberFormat("$#,##0_);($#,##0)");

Calculated Items

Calculated items are pivot table items that use custom formulas containing constants or refer to other items in the pivot table. These items can be added to the row or column field area of the pivot table but do not exist in the source data.

In DsExcel, ICalculatedItems interface represents the collection of calculated items in a particular pivot table. You can fetch this collection of pivot items by using IPivotField.getCalculatedItems method. To add calculated items to a pivot table, the ICalculatedItems interface provides add method which accepts name and formula of the item as parameters. You can also use IPivotItem.setFormula method for setting the formula of a calculated item. To remove a calculated item from the ICalculatedItems collection, you can use remove method which accepts name of the target field as parameter. Pivot cache manages all the calculated items, hence changing a calculated item affects all pivot tables using same cache in the current workbook. Also, any kind of addition, deletion or change in calculated item triggers the pivot table update.

Refer to the following code to create calculated items in the pivot table:

// Get the calculated item for the specified field
ICalculatedItems countryCalcItems = calculatedItemTable.getPivotFields().get("Country").getCalculatedItems();
ICalculatedItems productCalcItems = calculatedItemTable.getPivotFields().get("Product").getCalculatedItems();

// add some calculated items using formulas
countryCalcItems.add("Oceania", "=Australia+NewZealand");
countryCalcItems.add("America", "=Canada");
IPivotItem myPivotItem = countryCalcItems.add("Europe", "=France");
        
// Change the formula of the calculated item
myPivotItem.setFormula("=France+Germany");
        
// Add calculated item using constant value
productCalcItems.add("IPhone 13", "=2500");
        
// Get the calculatedItems count
System.out.println("Calculated Items count: " + countryCalcItems.getCount());
        
// Remove a calculated item
countryCalcItems.remove("America");

Show Value As

While analyzing spreadsheet data, instead of comparing exact values, you may want to compare the values in terms of calculations. For instance, there are many ways to evaluate performance of a sales employee. You can compare his sales with target, sales as a percentage of total sales or sales in comparison to previous month's sale etc. To achieve these calculations easily, DsExcel provides "Show Value As" option which allows you to perform custom calculations in a pivot table by using several predefined formulas such as ＾% of Parent Total￣ or ＾% of Grand Total￣.

DsExcel Java provides setCalculation method of IPivotField interface which accepts values from PivotFieldCalculation enumeration for setting the predefined calculations. You can also set the base field and base field item to perform these calculations using setBaseField and setBaseItem methods respectively.

Refer to the following example code which demonstrates the value as percent of Australia.

IPivotTable percentOfTable = percentOfSheet.getPivotTables().add(pivotCache, percentOfSheet.getRange("A1"));
percentOfTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField);
percentOfTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField);
percentOfTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField);
percentOfTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField);

// set show value as, base field, base item.
IPivotField percentOfTableDataField = percentOfTable.getDataFields().get("Sum of Amount");
percentOfTableDataField.setCalculation(PivotFieldCalculation.PercentOf);
percentOfTableDataField.setBaseField("Country");
percentOfTableDataField.setBaseItem("Australia");

percentOfSheet.getRange("A:I").autoFit();