Pivot data measures and calculated measures

Measures let you perform simple operations on the data in a column and display the results in the pivot. You can use two types of measures:

Data measure - A data measure represents an operation on the data in a column or set of columns. You can use the operations defined in the workbook.Aggregation enum to aggregate the column data in different ways. For example, you can use the workbook.Aggregation.COUNT_DISTINCT value to count the number of distinct values that appear in a column.
Calculated measure - A calculated measure represents a value that is calculated based on other column values or data measures. For example, you can calculate the difference between aggregated values in two columns.

In this tutorial, you will create three data measures (using workbook.createDataMeasure(options)) and one calculated measure (using workbook.createCalculatedMeasure(options)). The first data measure counts the number of distinct values in the Tranline ID column in the dataset. Similar to creating data dimension items, you must provide an expression that represents the dataset column to create the measure for.

          var countMeasure = nWorkbook.createDataMeasure({
    label: 'Count',
    expressions: [dataset.getExpressionFromColumn({
        alias: 'TranlineID'
    })],
    aggregation: 'COUNT_DISTINCT'
});

The second data measure sums the values in the Credit column in the dataset. Again, we need to provide an expression that represents the column, but we use workbook.createExpression(options) to create a custom expression (instead of obtaining one from a column using Dataset.getExpressionFromColumn(options)). When you use this method to create an expression, you can specify a function that represents the operation used in the expression using the values in workbook.ExpressionType. Each function accepts different parameters.

In our case, we use the SIMPLE_CONSOLIDATE function and provide an expression representing the column to consolidate. This function consolidates a currency expression based on the user's subsidiary and current accounting period.

          var sumCredit = nWorkbook.createDataMeasure({
    label: 'Sum Credit',
    expression: nWorkbook.createExpression({
        functionId: workbook.ExpressionType.SIMPLE_CONSOLIDATE,
        parameters: {
            expression: dataset.getExpressionFromColumn({
                alias: 'Credit'
            })
        }
    }),
    aggregation: 'SUM'
});

The third data measure sums the values in the Debit column in the dataset. We use the same approach that we used for the Credit column.

          var sumDebit = nWorkbook.createDataMeasure({
    label: 'Sum Debit',
    expression: nWorkbook.createExpression({
        functionId: workbook.ExpressionType.SIMPLE_CONSOLIDATE,
        parameters: {
            expression: dataset.getExpressionFromColumn({
                alias: 'Debit'
            })
        }
    }),
    aggregation: 'SUM'
});

Now we can create our calculated measure. This measure calculates the difference between the Sum Credit and Sum Debit data measures that we previoulsy created. It uses the workbook.ExpressionType.MEASURE_VALUE function to obtain the values of each data measure, then it uses the workbook.ExpressionType.MINUS function to calculate the difference.

          var calculatedMeasure = nWorkbook.createCalculatedMeasure({
    label: 'Balance',
    expression: nWorkbook.createExpression({
        functionId: workbook.ExpressionType.MINUS,
        parameters: {
            operand1: nWorkbook.createExpression({
                functionId: workbook.ExpressionType.MEASURE_VALUE,
                parameters: {
                    measure: sumCredit
                }
            }),
            operand2: nWorkbook.createExpression({
                functionId: workbook.ExpressionType.MEASURE_VALUE,
                parameters: {
                    measure: sumDebit
                }
            })
        }
    })
});

Related Topics