There are three available tools for aggregation and transformation in Dataset Viewer depending on the issue and dataset type:
- Time transformation and aggregation for regular datasets
- Dimension aggregation in regular datasets
- Measure aggregation in flat datasets
1. Time transformation and aggregation for regular datasets
By default, it displays only transformation functions for your selected series:
Functions can transform time series from its basic value to “% change, YoY”, “moving average” etc. Description of each function can be found in "How to add a calculation"
The second ability is to set frequency of data, that is not presented in dataset within aggregation. E.g. if dataset has only daily data, this tool can aggregate it to any frequency higher. Example below shows how daily S&P500 Index is aggregated to quarterly points.
Once the frequency is aggregated, a dropdown list of the tool now will have options for aggregation. Options are sum, average and last value. The selected way of aggregation operates with initial daily values for each period of the selected new frequency. In our S&P 500 case last value is set for each quarter.
Moreover, changing frequency within aggregation of data can be combined with previous transformation, so that its possible to get YoY, %change based on frequency that was not provided in dataset.
2. Dimension aggregation in regular datasets
In a regular dataset, each dimension has icon “∑” – turning on/off dimension aggregation. First of all this tool helps unite elements into one time series summarizing their values. Select several elements, then click on dimension aggregation button, the tool will sum values for selected elements and display one time series. Example below shows selected indicator as sum for 3 selected countries.
Warning: remember that time series can have different units. In example above selected indicator is in US$ for each country.
Second case of using dimension aggregation is useful for parameterization on dashboards.
Dimension aggregation turned on without selected elements equals sum of “selected all” elements. How can it be used within parameterization?
In regular datasets selection must have selected elements in each dimension to build a chart. Lets say we have dataset with “company” dimension, dimension with “stock exchange” and “indicator” dimension. Each company has only one stock exchange connected. So select all for “stock exchange” is the same as just 1 element according to selected company.
Example below shows that selecting particular company leads to shortening available elements on stock exchange dimension.
As example, the idea is to parameterize some chart where company is a kind of dashboard filter, so after selecting any company our chart should change data for new filter. If we select a particular company and according to it stock exchange during creating a chart, parameterization by company will not work right way. Because once company of another stock exchange selected, chart will not find the data.
But if dimension of stock exchange will be aggregated without selection, that means selecting any company chart will always show sum of all stock exchange elements, but since each company has only one connected element, it will cover the goal successfully.
3. Measure aggregation in flat datasets
Flat datasets have special dimension “measure” where aggregation functions are available. Flat datasets do not have restriction that each dimension must have something selected to build a chart. So aggregation measure do functions like sum, average, min, max, count through all the data and time selected.
For example if measure is the only dimension with selected element, and time dimension set as all time – it will show 1 value as result of function through all the dataset.
Time can be changed to standard view options like range, since, all data etc., this way it will provide calculating values for all periods, selecting elements in dimensions works as well. The key moment here is that final values are not time series, it is calculation result of aggregating function through all the dataset or through your selection.
Aggregation measure for flat dataset works the same way creating a chart in dashboard builder. If chart editor will be switched to custom mode, this aggregation will look like calculation.
View after switching to customize mode