Grouping Data

Objectives

After completing this lesson, you will be able to:

  • Group data

Manage Groups

You can use the Group function in Web Intelligence to group values returned by an object into categories. These categories can then be used as the basis for aggregating measures at higher levels.

For example, the eFashion.unx universe contains Quarter and Quantity sold objects, which allow you to analyze the quantity sold totals for each quarter. However, if your reporting requirement is to display the quantity sold measure aggregated at the half-year of details, then the universe does not provide the objects that you need to meet this requirement.

By creating a Half Year variable based on the Quarter object, you can aggregate quantity sold totals for each half of the year. This data can then be presented in the same report with sold totals for each quarter.

The If() Function to Create Groups

Sometimes the Group function is not suitable for grouping data values, such as when you have too many values to select manually. In this situation, you can group by using the If() formula function.

For example, if you need to display revenue totals in both a table and a 3D pie chart aggregated for each of the eFashion performance types. The three performance types are Top Performers, Average Performers, and Low Performers.

Business Rules for Sales Revenue Performance

Each store is assigned to the appropriate category based on the following business rules:

Sales Revenue PerformanceSales Revenue
Top PerformersGreater than $7,500,000
Average PerformersBetween $2,000,000 and $7,500,000
Low PerformersLess than $2,000,000

You need to display revenue figures including subtotals in the table and revenue breakdowns in the chart.

Begin by creating a table using the State and Sales revenue objects.

Next, create a Sales Revenue Performance dimension variable using one of the following formulas:

Code snippet
=If([Sales revenue] > 7500000 ; "Top Performers" ;
If([Sales revenue] Between (2000000 ; 7500000) ; "Average Performers" ; "Low Performers"))
Expand

Or

Code snippet
=If [Sales revenue] > 7500000 Then "Top Performers"
ElseIf [Sales revenue] Between (2000000 ; 7500000)
Then "Average Performers"
Else "Low Performers"
Expand

If you add Sales Revenue Performance to the report, apply a break on Sales Revenue Performance, and insert Sum on the Sales revenue column, you create the first block.

To insert the chart, insert the 3D Doughnut chart into the report, and insert the Sales revenue measure on the Y-axis and the Sales Revenue Performance dimension on the X-axis.

Group Data

Log in to track your progress & complete quizzes