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 the 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 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 2017 - 2019 in different countries across products belonging to product groups and different channels. Other than the time dimension (Year → Quarter → Month), we have the following dimensions:

Configuring the Data

We want to see aggregate results for our business. We use the configured 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 dimensional 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 that 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 a task. After adding the line chart of the node “Inflation”, we open the “Configure Axes” dialog and add the 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 location (average inflation for all countries) coincides with the total (brown color) and is hidden underneath it. The total in January is calculated in the 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 dimensional 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 location (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 afterward add the desired nodes using the tile settings. If you want to display a dimension (or dimension level) that 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” dialog and add both nodes in the node's 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