How to show value and percentage difference between current and previous year in Blazor Pivot Table?

3 mins read

Introduction

In certain business scenarios, it may be essential to present data comparisons from different periods (for example, the current and previous year) in a pivot table. A clear presentation of such data comparisons enables decision-makers to identify insightful trends and patterns. This article demonstrates the process of creating calculated fields to display these differences and utilizes the AggregateCellInfo event for dynamic calculation within the Blazor Pivot Table.

Create calculated fields

In order to show the value and percentage differences between the current and previous year, you must create two calculated fields. The first field should represent the difference in value, while the other field will show the percentage difference. Below is a code sample that illustrates how you can accomplish this:

[index.razor]

<SfPivotView @ref="Pivot" TValue="ProductDetails" Height="500" AllowCalculatedField="true">
    <PivotViewDataSourceSettings DataSource="@data">
        <PivotViewColumns>
            <PivotViewColumn Name="Year"></PivotViewColumn>
        </PivotViewColumns>
        <PivotViewRows>
            <PivotViewRow Name="Country"></PivotViewRow>
        </PivotViewRows>
        <PivotViewValues>
            <PivotViewValue Name="Sold" Caption="Unit Sold"></PivotViewValue>
            <PivotViewValue Name="ValueDifference" Caption="ValueDifference"></PivotViewValue>
            <PivotViewValue Name="PercentageDifference" Caption="PercentageDifference"></PivotViewValue>
        </PivotViewValues>
        <PivotViewFormatSettings>
            <PivotViewFormatSetting Name="PercentageDifference" Format="P"></PivotViewFormatSetting>
        </PivotViewFormatSettings>
        <PivotViewCalculatedFieldSettings>
            <PivotViewCalculatedFieldSetting Name="ValueDifference" Formula="0"></PivotViewCalculatedFieldSetting>
            <PivotViewCalculatedFieldSetting Name="PercentageDifference" Formula="0"></PivotViewCalculatedFieldSetting>
        </PivotViewCalculatedFieldSettings>
    </PivotViewDataSourceSettings>
</SfPivotView>

Calculate the value and percentage difference between the current and previous year

Once you have created the necessary calculated fields, you need to use the AggregateCellInfo event. This event is triggered during the rendering of each cell in the pivot table and allows you to compute both the actual value and the percentage differences between the current and previous years. Below is a code sample that illustrates how you can accomplish this:

[Index.razor]

<SfPivotView @ref="Pivot" TValue="ProductDetails">
	<PivotViewEvents TValue="ProductDetails" AggregateCellInfo="AggregateCellInfo"></PivotViewEvents>
</SfPivotView>

@code {
    double? previousValue = 0;
    double? currentValue = 0;
    double? rowIndex = null;
    private void AggregateCellInfo(AggregateEventArgs args)
    {
        double changed;
        dynamic changedPercentage;

        // Here we get the current row index
        if (rowIndex == null)
        {
            rowIndex = args.Row.RowIndex;
        }
        else if (args.Row.RowIndex != rowIndex)
        {
            previousValue = 0;
            rowIndex = args.Row.RowIndex;
        }
        if (args.FieldName == "Sold")
        {
            if (previousValue == 0)
            {
                previousValue = args.Value;
                currentValue = null;
            }
            else
            {
                currentValue = args.Value;
            }
        }
        // Here we calculate the actual difference between current and previous year
        if (args.FieldName == "ValueDifference" && currentValue != null)
        {
            changed = (double)previousValue - (double)currentValue;
            args.Value = changed;
        }
        // Here we calculate the percentage difference between current and previous year
        else if (args.FieldName == "PercentageDifference" && currentValue != null)
        {
            changedPercentage = ((double)previousValue - (double)currentValue) / (double)currentValue;
            args.Value = changedPercentage;
            previousValue = currentValue;
        }
        // If the current value is null we set the cell as empty
        if (currentValue == null)
        {
            if (args.FieldName == "ValueDifference")
            {
                args.Value = null;
            }
            else if (args.FieldName == "PercentageDifference")
            {
                args.Value = null;
                args.AllowFormatting = false;
            }
        }
    }
}

Here’s a breakdown of how the code works:

First, we define three variables: previousValue, currentValue, and rowIndex. The previousValue stores the value from the previous year, while currentValue holds the value for the current year. Meanwhile, rowIndex is used to store the current cell’s row index.
Subsequently, within the AggregateCellInfo event, we check whether the current field name is “Sold”. If it is, we store the values of the previous and current years in the previousValue and currentValue variables, respectively.
Following this, we check whether the current field name is “ValueDifference” and if the currentValue is not null. If both conditions are met, we calculate the actual value difference between previousValue and currentValue and assign it to the current cell value using the args.Value property. This action will accurately show the actual value difference between the years.
Likewise, if the current field name is “PercentageDifference”, we calculate the percentage difference between previousValue and currentValue and assign it to the current cell value. This effectively displays the percentage difference between the years.
If currentValue is null, implying that a comparison isn’t possible (for example, for the first data point where there isn’t any data from the previous year), both the “ValueDifference” and “PercentageDifference” field values are assigned null to show an empty cell.

The following screenshot, which portrays the results of the code snippet mentioned above,

Screenshot

You can refer to this GitHub sample for a practical demonstration of this code.

Conclusion

I hope you enjoyed learning how to show value and percentage difference between current and previous year in a Blazor Pivot Table.

You can also refer to our Blazor Pivot Table feature tour page to learn about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our Pivot Table example to understand how to create and manipulate data.

For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.

If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, support portal, or feedback portal. We are always happy to assist you!