Easily Group Data into Ranges in Web Applications Using Pivot Table
Live Chat Icon For mobile
Live Chat Icon
Popular Categories.NET  (182).NET Core  (26).NET MAUI  (233)Angular  (115)ASP.NET  (49)ASP.NET Core  (80)ASP.NET MVC  (85)Azure  (42)Black Friday Deal  (1)Blazor  (243)BoldSign  (15)DocIO  (24)Essential JS 2  (108)Essential Studio  (198)File Formats  (73)Flutter  (140)JavaScript  (227)Microsoft  (121)PDF  (86)Python  (1)React  (107)Streamlit  (1)Succinctly series  (131)Syncfusion  (988)TypeScript  (32)Uno Platform  (3)UWP  (3)Vue  (45)Webinar  (54)Windows Forms  (59)WinUI  (72)Word  (18)WPF  (165)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  (154)Chart of the week  (62)Cloud  (16)Company  (440)Dashboard  (8)Data Science  (3)Data Validation  (8)DataGrid  (74)Development  (696)Doc  (7)DockingManager  (1)eBook  (99)Enterprise  (22)Entity Framework  (7)Essential Tools  (13)Excel  (47)Extensions  (31)File Manager  (8)Gantt  (23)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  (530)Mobile MVC  (9)OLAP server  (2)Open source  (1)Orubase  (12)Partners  (21)PDF viewer  (48)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  (426)Uncategorized  (68)Unix  (2)User interface  (67)Visual State Manager  (2)Visual Studio  (31)Visual Studio Code  (19)Web  (650)What's new  (355)Windows 8  (19)Windows App  (2)Windows Phone  (15)Windows Phone 7  (9)WinRT  (26)

Easily Group Data into Ranges in Web Applications Using Pivot Table

Frequency distribution categorizes and positions input data in appropriate groups. This occurs in ranges for better statistical analysis. A pivot table is a type of component that allows users to categorize and view input data like in a frequency distribution table. You can group date, number, and text fields in a pivot table into ranges.

For example:

  • We can group the birth rate around the world over the last three years, quarter by quarter (date type), into 2019 (Q1, Q2, Q3, Q4), 2020 (Q1, Q2, Q3, Q4), and 2021 (Q1, Q2, Q3, Q4).
  • We can divide the world population into age groups (number type) such as 0-20, 21-40, 41-60, 60-and-above.
  • We can also group populations of countries (string type) into the continents of North America, South America, Europe, Asia, Africa, and Australia.

In this blog, we will walk through how to group data into ranges with a real-time scenario.

Group data using date and time

To begin with, we will group data based on date and time ranges. In this illustration, we will see YouTube videos published by a channel in English and Hindi and the number of likes and comments for them during specific time periods.

First, insert the Years field into the pivot table’s row axis, the Language of the Video field into the column axis, and Video URL (to get the number of videos), Likes, and  Comments into the value axis.

When you right-click on the Years field, a context menu with the option Group appears. When you select Group, a dialog appears in which you can enter the start and end dates, as well as the interval type. Since we are grouping by date type, the Interval dropdown will display the following options: years, quarters, months, days, hours, minutes, and seconds. As a result, we will get the following output.

Group option in the context menu to group by date and time
Group option in the context menu to group by date and time
Group dates from 2005 to the present by year, quarter, and month
Group dates from 2005 to the present by year, quarter, and month
Pivot Table displaying YouTube video details organized by date and time
Pivot Table displaying YouTube video details organized by date and time

Note: By default, the dates excluded from the date range will be grouped under the Out of Range category. We can also customize the name Out of Range.

Group data using number type

Following the date and time is the number type. In this illustration, we will see used car sales divided into ranges of kilometers driven for various car brands. To make the classification, the number of cars in these ranges are tabulated along with sales amount details: 0–100,000 kms, 100,001–200,000 kms, 200,001–300,000 kms, and greater than 300,000 kms.

To do this, insert the kms Driven field in the pivot table’s column axis, Car Name in the row axis, and No. of cars sold and Sold amount in the value axis.

When you right-click the kms Driven field, a context menu with the Group option will appear. When you select Group, a dialog appears in which you can enter the start and end numbers, as well as the interval count. Refer to the following output images.

Group option in the context menu to group by kilometers
Group option in the context menu to group by kilometers
Kilometers from 0 to 300,000 are divided into groups of 100,000
Kilometers from 0 to 300,000 are divided into groups of 100,000
Pivot Table displaying information about used car sales
Pivot Table displaying information about used car sales

Note: By default, the numbers excluded will be grouped under the Out of Range category. The name Out of Range can be customized.

Group data by string type

Finally, we’ll use the custom format to format the string data type. To illustrate this, we will return to the previous example of used car sales, where car names of string data type are grouped based on their brand name.

Insert the kms Driven field in the pivot table’s column axis, Car Name in the row axis, No. of cars sold and Sold amount in the value axis.

To group the custom format field members, select the appropriate header(s) by holding down the Ctrl or Shift key and right-clicking; a context menu with the option Group appears. When you select Group, a dialog box appears in which you can enter the field caption and group name. In our illustration, we enter Brand in the field caption and Audi in the group name, resulting in the creation of a new field, Brand, in the row axis on top of the Car Name field. And the selected header(s) are added to the group created under the group name Audi. We get the following output.

Pivot Table displaying information about used car sales
Pivot Table displaying information about used car sales
Group option in the context menu to group Audi car names
Group option in the context menu to group Audi car names
With the new Brand field, group car names under the name Audi
With the new Brand field, group car names under the name Audi
Pivot Table displaying all Audi car names grouped under Audi
Pivot Table displaying all Audi car names grouped under Audi

Audi car names are now grouped under one name, Audi, while other car names are listed under their names, such as Ambassador CLASSIC 1500 DSL AC, on top of Audi. You can group Ambassador cars under their brand. To do so, expand the appropriate headers and select the car names. As usual, right-click and select Group. Retype Brand in the field caption and Ambassador in the group name.

Group option in the context menu to group other car names by brand.
Group option in the context menu to group other car names by brand
With the existing Brand field, group car names under the Ambassador brand
With the existing Brand field, group car names under the Ambassador brand

You can do the same for all other car names, and the end result is shown in the following images.

Pivot Table displaying information on used car sales by brand
Pivot Table displaying information on used car sales by brand
Pivot Table displaying information on used car sales by brand
Pivot Table displaying information on used car sales by brand

Ungrouping

You can ungroup applied date, number, and string grouping by right-clicking the appropriate header and selecting Ungroup from the context menu in the Pivot Table.

Clearing the grouped data using the context menu UI
Clearing the grouped data using the context menu UI

Editing groups

You can edit the date- and number-based grouping by right-clicking and selecting Group from the context menu. The existing grouping information is preserved (state persisted) and we can modify that at any time.

In the case of a custom format, such as string, we have to remove the newly created field, and restart grouping from the beginning. There is no direct edit or modify option for the custom format.

Documentation references

Easily build real-time apps with Syncfusion’s high-performance, lightweight, modular, and responsive JavaScript UI components.

Conclusion

We hope this article gave you a better understanding of grouping data into ranges in the Pivot Table. Try it out and leave your feedback in the comments section.

Thanks for reading!

Our Pivot Table is available in our Blazor, ASP.NET (Core, MVC, Web Forms), JavaScript, Angular, React, and Vue component suites. Use it to organize and summarize business data elegantly in any application!

For existing customers, the newest version of Essential Studio is available for download from the License and Downloads page. If you are not yet a Syncfusion customer, you can try our 30-day free trial to check out the available features.

For questions, you can contact us through our support forum, support portal, or feedback portal. We are always happy to assist 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