Have any questions?
+44 1234 567 890
Chapter 7 The Pivot Mode of the Dynamic List
7. The Pivot Mode of the Dynamic List
In the chapter Dynamic lists, we presented the individual areas of the dynamic list in detail. In the following chapter, we will introduce a second mode of the dynamic list that allows you to analyze data, prepare it hierarchically or as a crosstab and create charts: the pivot mode of the dynamic list.
To switch a dynamic list to pivot mode, select Pivot from the toolbar and confirm the prompt that appears. The view is reloaded in pivot mode and you can see that the Dimensions/Key figures aspect has now replaced the List configuration aspect. The Parameters (if available), Calculated fields and Pre-filter aspects remain available and have the same meanings as in normal dynamic list mode.
7.1 Introduction
The pivot function can be described most simply as an extension of aggregation. Let's look at a simple example of a product survey. The following table describes the data set of the dynamic list and contains data records of a product survey. Each row corresponds to an evaluation of a product by a person. In addition to the product category and product, the gender and an age grouping of the person surveyed as well as the rating are stored.
| Product category | Product | Gender | Age | Rating |
| Electric | Television | m | 40+ | 5 |
| Electric | Television | m | 40+ | 3 |
| Electric | Television | f | 40+ | 4 |
| Electric | Television | m | 20-40 | 5 |
| Electric | Television | m | 20-40 | 4 |
| Electric | Television | f | 20-40 | 3 |
| Electric | Television | m | 20-40 | 2 |
| Electric | Refrigerator | m | 20-40 | 2 |
| Electric | Refrigerator | m | 20-40 | 5 |
| Electric | Refrigerator | m | 20-40 | 5 |
| Electric | Refrigerator | f | 20-40 | 4 |
| Electric | Refrigerator | m | 20-40 | 3 |
| Food | Muesli | m | 20-40 | 5 |
| Food | Muesli | m | 20-40 | 3 |
| Food | Muesli | m | 40+ | 4 |
| Food | Muesli | f | 20-40 | 3 |
| Food | Chocolate | f | 20-40 | 5 |
| Food | Chocolate | f | 20-40 | 3 |
| Food | Chocolate | f | 20-40 | 5 |
| Food | Chocolate | m | 20-40 | 4 |
| Food | Chocolate | m | 20-40 | 5 |
| Food | Chocolate | f | 20-40 | 2 |
| Food | Chocolate | m | 40+ | 3 |
| Food | Chocolate | m | 40+ | 4 |
| Food | Chocolate | f | 40+ | 3 |
| Product category | Product | Rating |
| Electric | Television | 3,71 |
| Refrigerator | 3,8 | |
| Food | Muesli | 3,75 |
| Chocolate | 3,77 |
Aggregation allows you to aggregate the data using the dynamic list (see section 6.8.). To do this, you first determine the grouping characteristics and then the attributes that are to be aggregated. For example, grouping by product category and product and aggregating the evaluation using the mean value function.
In contrast to simple aggregation, pivot mode gives you extensive options for quickly creating, changing and arranging aggregations differently. On the one hand, this is a powerful tool for analyzing data in order to develop an understanding of the database, but it also provides new and intuitive forms of presentation.
For example, you can display the above aggregation as a cross table in order to additionally prepare the data according to the gender of the respondents:
| Male | Female | ||
| Electric | Television | 3,8 | 3,5 |
| Refrigerator | 3,75 | 4 | |
| Food | Muesli | 4 | 3 |
| Chocolate | 4 | 3,6 |
In addition to crosstabs, the pivot table allows you to quickly change the aggregation level, e.g. you can switch to the product category level with a simple mouse click.
| Male | Female | |
| Electric | 3,8 | 3,7 |
| Food | 4 | 3,5 |
7.2 Working with Pivot
Working in pivot mode takes place in two steps. After switching the dynamic list to pivot mode, you first define in the Dimensions/Key figures aspect which attributes you want to work with and whether these attributes are grouping characteristics (dimensions) or aggregation key figures. To do this, use the column selection dialog known from the dynamic list. The selected columns/attributes are then displayed in a list.
Name
By default, attributes are assigned the technical name or, if available, a preset name. To rename attributes, click in the Column name cell, as in the list configuration, and assign a new plain text name.
Aggregation
To define an attribute as an aggregation key figure (in the example above, this would be the attribute rating), set an aggregation function on the attribute. The same aggregation functions are available here as in the normal dynamic list.
Dimensions
All attributes that are not marked as aggregation indicators are regarded as possible grouping characteristics. A special feature is that grouping characteristics are defined along a common dimension. In the example above, this is the case for product category and product. Other examples are geographical characteristics (e.g. continent, country, city) or temporal characteristics (year, month, day). To mark characteristics that belong together, enter the same plain text name in the Dimension column, e.g. Product for the characteristics Product category and Product. In such a case, the order of the characteristics is important and they should be sorted from coarse to fine, i.e. in the example, the product category should be above the product.
Once dimensions and aggregation key figures have been configured, you can switch to the Pivot/Preview aspect, where further configuration takes place. Please note that the Calculated fields and Pre-filter aspects function identically to the Dynamic list. Pre-filters restrict the entirety of the data. Calculated fields can be used as dimensions or aggregation key figures.
The actual pivot view is presented in the following chapter The analysis view (Chapter 8.). The analysis view is shared by the pivot mode of the dynamic list and OLAP reports (see chapter 4.).
After making changes in one of the aspects, the analysis view (see chapter 8.) must be reloaded. To do this, use the reload icon (rotating arrow) from the toolbar in the analysis view.