Manage data in a visualization
After you add a visualization widget to a storyboard, you need to define the data to be shown in the visualization.
- You added a visualization widget to a storyboard. For details, see Add widgets.
- The widget settings pane is open.
- You selected a dataset for the visualization.
On the data tab, manage the data items as needed:
To add dimensions and measures, you can do the following:
Click advanced options to view the whole list of measures and dimensions for the selected dataset. Then, drag dimensions and measures to the corresponding data fields.
If the column has a description, it appears in the tooltip.
Click in a field under the Data section, and then select an item from the list. By clicking the More columns link at the end of the list, you open the whole list of measures and dimensions for the selected dataset.
Click in a field under the Data section, and then start typing to search for the needed column. You can search only for the columns of the corresponding data role. For example, in the Dimensions field, you will not see measures matching the entered phrase. By clicking the More columns link at the end of the list, you open the whole list of measures and dimensions for the selected dataset.
Note: To ensure that all datasets (your own and shared with you) are shown in the drop-down list, click Refresh the dataset list above the field.
Note: The number of measures and dimensions that you can add to each data field depends on the visualization type.
If applicable, you can also use dimensions as measures or measures as dimensions. However, this does not apply to calculations: a measure calculation cannot be used as a dimension, and a dimension calculation cannot be used as a measure. In this case, you can create two calculations with different data types.
For cubes and Cognos data sources, dimensions cannot be used as measures.
You can aggregate the dimensions that are related to date and time (for example, Sales Date, Arrival Time). To do that, click a dimension, and then select an aggregation type. The aggregation is specified in parentheses after the dimension name.
After you click Visualize, the data is grouped based on the aggregation type. For example, the data is grouped differently for the Sales Date dimension, depending on the selected aggregation types:
- Month – The visualization groups data by Jan 2017, Feb 2017, Mar 2017, and so on.
- Month name – The visualization groups the data by Jan, Feb, Mar, and so on.
For your convenience, the drop-down list with aggregation types also includes examples based on the current date.
For the columns with time, you can view the time-related aggregations, such as hours, minutes, and seconds.
For the columns with date and time, you can view the date and time-related aggregations.
Important: The Default aggregation option for the dates does not aggregate them but only applies a selected format to each record. For example, let’s say you have the three dates: January 1, January 2, and August 1. You select Default and format them as “Year Quarter.” As a result, you get the following three records: 2021 Q1, 2021 Q1, and 2021 Q3.
For details, see Format dates & numbers.
You can aggregate the dimensions that are used in measure fields. To do that, click a dimension in a measure field, and then select an aggregation type. The aggregation is specified in parentheses after the dimension name:
- Count – Total number of rows containing the selected dimension.
- Count (distinct) – The number of unique values of the selected dimension. Repetitive values are not counted.
Additionally, dimensions of numeric or integer data types have all aggregation types that are available for measures.
Note: For cubes and Cognos data sources, dimensions cannot be used as measures.
The aggregation type that is defined at the time of a dataset creation is a default type for that measure in a visualization.
To change the aggregation type, click the measure, and then select another type from the drop-down list. The aggregation type is specified in parentheses after the name of the measure.
For details on aggregation types, see About aggregation for measures.
By creating calculations, you add new columns to a dataset. For example, you may need to create the Gross Sales Variance measure for a visualization. To do that, you enter the calculation as [Gross Sales] - [Planned Gross Sales], where the “Gross Sales” and “Planned Gross Sales” are the existing measures in your dataset.
To add a custom data science calculation by using a script, see Add script calculations.
You can change the format of dates and define how numeric values are displayed for a certain column. For details, see Format dates & numbers.
To view information about a certain column (dimension or measure), do the following:
For details on the concepts, see About dimensions and measures.
You can modify the data defined in a visualization at any time if you are in edit mode. For details, see Visualization settings (data tab).