Transforming Raw Data with Pivot Table Aggregation
Live Chat Icon For mobile
Live Chat Icon
Popular Categories.NET  (181).NET Core  (28).NET MAUI  (227)Angular  (113)ASP.NET  (49)ASP.NET Core  (81)ASP.NET MVC  (87)Azure  (42)Black Friday Deal  (1)Blazor  (236)BoldSign  (14)DocIO  (24)Essential JS 2  (110)Essential Studio  (200)File Formats  (73)Flutter  (136)JavaScript  (226)Microsoft  (122)PDF  (83)Python  (1)React  (105)Streamlit  (1)Succinctly series  (131)Syncfusion  (964)TypeScript  (33)Uno Platform  (3)UWP  (3)Vue  (46)Webinar  (53)Windows Forms  (59)WinUI  (72)WPF  (163)Xamarin  (159)XlsIO  (38)Other CategoriesBarcode  (5)BI  (29)Bold BI  (8)Bold Reports  (2)Build conference  (11)Business intelligence  (55)Button  (4)C#  (164)Chart  (147)Chart of the week  (58)Cloud  (15)Company  (440)Dashboard  (8)Data Science  (3)Data Validation  (8)DataGrid  (73)Development  (681)Doc  (7)DockingManager  (1)eBook  (99)Enterprise  (22)Entity Framework  (7)Essential Tools  (13)Excel  (43)Extensions  (23)File Manager  (7)Gantt  (21)Gauge  (12)Git  (5)Grid  (31)HTML  (13)Installer  (2)Knockout  (2)Language  (1)LINQPad  (1)Linux  (2)M-Commerce  (1)Metro Studio  (11)Mobile  (522)Mobile MVC  (9)OLAP server  (2)Open source  (1)Orubase  (12)Partners  (21)PDF viewer  (45)Performance  (13)PHP  (2)PivotGrid  (5)Predictive Analytics  (6)Report Server  (3)Reporting  (8)Reporting / Back Office  (9)Rich Text Editor  (12)Road Map  (12)Scheduler  (54)Security  (5)SfDataGrid  (9)Silverlight  (21)Sneak Peek  (32)Solution Services  (4)Spreadsheet  (11)SQL  (15)Stock Chart  (1)Surface  (4)Tablets  (5)Theme  (12)Tips and Tricks  (112)UI  (406)Uncategorized  (68)Unix  (2)User interface  (68)Visual State Manager  (2)Visual Studio  (31)Visual Studio Code  (19)Web  (628)What's new  (341)Windows 8  (19)Windows App  (2)Windows Phone  (15)Windows Phone 7  (9)WinRT  (26)
Transforming Raw Data with Pivot Table Aggregation

Transforming Raw Data with Pivot Table Aggregation

A pivot table puts together raw data from various data sources that are normally flat, resulting in many individual and detailed records that are unorganized. When raw data lacks structure, it isn’t easy to understand for any user.

This is where the Syncfusion JavaScript Pivot Table’s data aggregation feature comes in. This feature converts raw data into summarized and organized data using a pivot engine. The data is then bound to a pivot table UI for viewing the well-structured and understandable result.

Let’s explore this powerful and user-friendly feature with code examples.

Aggregation types

The most popular aggregation types supported by the JavaScript Pivot Table are:

  • Sum
  • Product
  • Count
  • Distinct Count
  • Min
  • Max
  • Average

Other types of aggregations are:

  • Median
  • Index
  • PopulationStDev
  • SampleStDev
  • PopulationVar
  • SampleVar
  • RunningTotals
  • DifferenceFrom
  • PercentageOfDifferenceFrom
  • PercentageOfGrandTotal
  • PercentageOfColumnTotal
  • PercentageOfRowTotal
  • PercentageOfParentTotal
  • PercentageOfParentColumnTotal
  • PercentageOfParentRowTotal

Use case

Let’s look at a real-time scenario in a supermarket. Many sales occur there throughout the day. Every sale will be treated as a separate record added to the database, which is our raw data. It would be difficult to analyze product sales using raw data alone, and this raw data must be converted to a structured, organized format.

For instance, take the product Polo T-Shirt. If an analyst wants to know the minimum or maximum number of polo t-shirt purchases per month, quarter, or year, they can use the Min and Max aggregates. If the analyst wants to know the average sales of t-shirts per month, quarter, or year, they can use the Avg aggregate.

Points to remember

Before diving deep into data aggregation in our JavaScript Pivot Table, remember the following points:

  • Despite the fact that the Pivot Table supports many aggregation types, fields with data types such as numbers support all the listed aggregation types. Meanwhile, the fields with data types such as string, date, datetime, and Boolean support only count and distinct count aggregation types.
  • By default, the aggregation type will be Sum for number-type fields and Count for non-number-type fields.
  • The aggregation types are available only for fields in the Values section.

Getting started with data aggregation in JavaScript Pivot Table

The aggregation type can be set to a field in the Pivot Table via:

  • UI option: Changes the aggregation type dynamically at runtime.
  • Code-behind: Displays the aggregated field in the pivot table during initial loading.

Perform data aggregation via UI

 To do the data aggregation via UI, use the dropdown arranged inside the Values section.

Dropdown to view the aggregation type
Dropdown to view the aggregation type

A list of basic aggregation types will appear, which can be selected from and changed at runtime.

Menu from which to select the aggregation type
Menu from which to select the aggregation type

Click the More option at the bottom of the context menu. It opens a dialogue with advanced aggregation settings to handle a field based on your requirements.

Advanced aggregation settings in JavaScript Pivot Table
Advanced aggregation settings in JavaScript Pivot Table

So, these are the primary steps for dynamically changing an aggregation type for any field at runtime via UI.

Perform data aggregation via code-behind

To do the data aggregation via code-behind, in the Values section, first define the field with its name and caption.

The type property then accepts the assigned aggregate type and processes it in the pivot engine before providing the result data to render the pivot table UI.

Refer to the following code example. In it, we have used the aggregation type Avg.

import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';

let pivotTableObj: PivotView = new PivotView({
    dataSourceSettings: {
        dataSource: pivotData as IDataSet[],
        columns: [{ name: 'EnerType', caption: 'Energy Type' }, { name: 'EneSource', caption: 'Energy Source' }],
        values: [{ name: 'PowUnits', caption: 'Units (GWh)', type: 'Avg' }, { name: 'ProCost', caption: 'Cost (MM)' }],
        rows: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter', caption: ' Quarter Year' }],
        filters: []
    }
});
pivotTableObj.appendTo('#PivotTable');

Refer to the following images.

Units (GWh) field with Sum aggregation (default)
Units (GWh) field with Sum aggregation (default)
Units (GWh) field with Avg aggregation
Units (GWh) field with Avg aggregation

Customization options

The JavaScript Pivot Table’s data aggregation feature comes with more options:

  • The ability to show or hide the UI icon allows us to dynamically change the aggregation type at runtime. By default, the UI icon will be displayed.
  • Rather than displaying all aggregation types in the context menu, one can display only the desired types.
  • The ability to edit the aggregation text in the UI. For example, “Sum of Units Sold” can be displayed as “Units Sold” as well.

References

For more details, refer to the Aggregation in JavaScript Pivot Table demos and documentation.

Conclusion

Thanks for reading! This blog explored the data aggregation feature in the Syncfusion JavaScript Pivot Table. With this feature, we can convert raw data into summarized and organized data to get clear insights from it.

We strongly urge you to explore our Syncfusion Essential JS 2 suite, which boasts over 80 user-friendly JavaScript UI controls. Your feedback is essential to us, and we encourage you to share your thoughts in the comments section below.

Existing customers can access the newest version of Essential Studio from the License and Downloads page. If you’re not a customer yet, we offer a 30-day free trial to let you experience the full range of available features.

If you have questions, you can contact us through our support forumsupport portal, or feedback portal. Our team is always eager to help you.

Related blogs

Tags:

Share this post:

Popular Now

Be the first to get updates

Subscribe RSS feed

Be the first to get updates

Subscribe RSS feed