Configuring and Pivoting Data Displayed in Charts and Tables

If you are new to charts in Valsight, please read How to Create Charts & Tables and Sorting and Formatting Charts first.

Valsight works natively with high-dimensional data. For example, a single data point of Sales node can represent a combination of a country, month, product and channel dimensions (more on dimensionality). In many business cases, we want to combine different nodes into a table, each of them with different dimensionality and additionally, we want to see the values for different scenarios and their compression. In this article, you will learn about the ways to create such charts and tables.

Pivot Tables and Charts

The simplest case is charting of a single multidimensional node.  After creating a table or a chart, use the configure axis button to select the desired level of aggregation. Aggregation calculation (e.g. Sum or Average)  follows the Aggregation Settings set up previously in the model editor. Greyed-out dimension levels are not present in the node at hand. Scenarios and Assumptions can be added in the same way as the regular dimensions. The configuration works the same for Charts, Tables and YoY/Scenario Bridges.  

Example

Let's assume our data represents monthly clothing store Sales in quantity for of 2017 - 2019 in different countries across Products belonging to Product Groups and different Channels. Other than Time Dimension (Year → Quarter → Month), we have the following dimensions:

Dimension Flocation
Dimension Level FCountry
Dimension Level Value CH
Dimension Level Value DE
Dimension Level Value AT
Dimension Level Value Other
Dimension FProduct FProduct
Dimension Level FProduct-Group FProduct
Dimension Level Value Clothes T-Shirt
Dimension Level Value Clothes Pants
Dimension Level Value Accessories Sunglasses
Dimension FChannel
Dimension Level FChannel
Dimension Level Value Offline
Dimension Level Value Online

Configuring the data

We want to see aggregate results for our business. We use the configure axes to aggregate the data to Country, Product Group and Quarterly level disregarding the Channel. 

  Video

Dimensions can be moved between columns and rows per Drag and Drop:

  Video

Working with totals

There are two different ways to add Dimension Levels and Grand totals to your calculation. First, you can use the Display Grand Totals slider in the Axis Configuration menu. Or alternatively, you can include a higher level of your dimension, e.g. add Quarters to a Monthly Chart. If you are at the highest level (e.g. Years), you can add the "*Dimension Name* (Total)" in the Configure Axes drop-down.

Grand Totals performs the aggregation of the visible data. In contrast, the higher dimension levels and dimension totals display the aggregated total for all data belonging to it. Use Workspace filters (On the top of each Worksheet) if you want to achieve a different behavior.


Let's see how it works with an example of an Inflation node which has its aggregation type set to average. We want to see monthly Inflation development in different countries in the first half of 2017 as well as Quarterly and Overall averages (named Totals). The line chart is perfect for such task. After adding the line chart of the node Inflation, we open the Configure Axes Dialog and add following Dimension Levels into the X-Axis and Data Series:

The resulting chart looks like this:

There are a couple of things to note here. First, see that for the moment, the FLocation (Average inflation for all countries) coincides with the Total (brown colour) and is hidden underneath it. The Total in January is calculated in following way: (3+2+1-2)/4 = 1%. The quarterly averages make sense as well. The Value for Austria(AT) in 2017-Q1 is 4% which is an average of the 3,4 and 5 percent rates in the preceding months.  When we use the filter in the tile settings to keep only Germany and Austria and leave February and March out of our analysis, the different totals change:

Remember, the higher dimension levels and dimension totals always show totals for all data belonging to them no matter what data is visible. Therefore, the Inflation of Austria in Q1 remains at 4% and FLocation (the total for countries) still sits at 1% in the month of January. However, the Total data series value changed. It uses only the visible data as the calculation basis.

Combining different nodes in a single Chart or Table

With Valsight, you can also add different nodes into a single Chart or Table. To do that, just add the Nodes option in the Configure Axes Dialog and afterwards add the desired nodes using the tile settings. If you want to display a dimension (or dimension level) which is not present in all nodes selected, include the "*dimension name* (total)" for such dimension as well.

Let's say we want to display both Inflation and Sales in a Table, we would add nodes into the Configure Axes Dialogue and add both nodes in the nodes Settings.

Configuration

Result

To show sales once again at the product level and still retain the inflation numbers, we would need to add both FProduct(Total) and FProduct into our table.

Special Options 

With some specific settings, additional formatting is possible:

  • A table with nodes in rows has special formatting options described in Sorting and Formatting Charts as well as the possibility to create an automatic hierarchy.
  • Adding Assumptions to rows or columns gives two extra checkboxes: Creating YoY Bridges and showing assumption details




Was this helpful?

Yes | Somewhat | No