Calculate difference between values PivotData C# examples
This documentation describes usage of
DifferencePivotTable
(Toolkit component).
Let's assume that you already have IPivotTable instance with some values and need to display difference between these values (on rows or columns).
In same manner as for
percentages
it is possible to calculate differences by wrapping original pivot table model with DifferencePivotTable wrapper:
var basePvtTbl = new PivotTable(
new[]{"product"}, new[]{"year"}, pvtData);
var diffPvtTbl = new DifferencePivotTable(
basePvtTbl,
DifferencePivotTable.DifferenceMode.PreviousColumn);
It is possible to calculate a difference between rows or columns; also you can specify which value (previous or next) should be used:
DifferencePivotTable.DifferenceMode.PreviousColumnDifferencePivotTable.DifferenceMode.NextColumnDifferencePivotTable.DifferenceMode.PreviousRowDifferencePivotTable.DifferenceMode.NextRow
| 2015 | 2016 | 2017 | |
|---|---|---|---|
| Product1 | 15 | 17 | 9 |
| Product2 | 10 | 8 | 11 |
| Totals | 25 | 25 | 20 |
| 2015 | 2016 | 2017 | |
|---|---|---|---|
| Product1 | 2 | -8 | |
| Product2 | -2 | 3 | |
| Totals | 0 | -5 |
If pivot table has several measures it is possible to specify which measure should be calculated as the difference with
DifferencePivotTable constructor that accepts measure index as a 3rd parameter:
var percentPvtTbl = new DifferencePivotTable( basePvtTbl, DifferencePivotTable.DifferenceMode.PreviousColumn, 0);
DifferencePivotTable several times to calculate difference for specific measures.
It is possible to calculate difference as percentage by setting
DifferencePivotTable.Percentage to true;
in this case percent change is calculated in the same way as in Excel PivotTable by formula:
diff_percentage = (current_value-prev_value) / prev_value * 100
If "prev_value" is 0 percent change will be empty. If "current_value" is empty it is treated as 0.