Creating Derived Items

About Derived Items

For the categories and measures in your exploration, you can create derived data items. Derived data items are aggregated measures that perform calculations for your data.

Derived Item Types for Categories

For categories, you can create the following types of derived items:
Distinct Count
displays the number of distinct values for the category that it is based on.
Note: If your category contains missing values, then distinct count is increased by one.
Count
displays the number of nonmissing values for the category that it is based on.
NMiss
displays the number of missing values for the category that it is based on.

Derived Item Types for Measures

For measures, you can create the following types of derived items:
Difference from Previous Parallel Period
Displays the difference between the value for the current time period and the value for the previous parallel time period within a larger time interval. For example, you might derive the difference between sales for the current month and sales for the same month of the previous year.
Note: This derived item is not available if your data source does not contain a date data item that includes the year.
Difference from Previous Period
Displays the difference between the value for the current time period and the value for the previous time period. For example, you might derive the difference between sales for the current month and sales for the previous month.
Note: This derived item is not available if your data source does not contain a date data item that includes the year.
Percent Difference from Previous Parallel Period
Displays the percentage difference between the value for the current time period and the value for the previous parallel time period within a larger time interval. For example, you might derive the percentage difference between sales for the current month and sales for the same month of the previous year.
Note: This derived item is not available if your data source does not contain a date data item that includes the year.
Percent Difference from Previous Period
Displays the percentage difference between the value for the current time period and the value for the previous time period. For example, you might derive the percentage difference between sales for the current month and sales for the previous month.
Note: This derived item is not available if your data source does not contain a date data item that includes the year.
Percent of Subtotals
For crosstabs only, displays the percentage of the subtotal value for the measure on which it is based.
You can select whether to calculate the percentage of the subtotal value for the rows in the crosstab or for the columns in the crosstab.
For example, you might calculate the percentage of the row subtotal value for a measure that contains revenue values. The derived item displays the percentage of the subtotal of revenues for each row in the crosstab.
Note: Percent of Subtotals can be created only from within a crosstab visualization.
Percent of Total
Displays the percentage of the total value for the measure on which it is based. For example, you might derive the percentage of the total value for a measure that contains revenue values. If you create a bar chart by using the derived item and a category that contains products, then the bar chart shows the percentage of total revenue for each product.
Note: The percentage of the total value is relative to the subset of data that is selected by your filters and ranks.
Period to Date
Displays the aggregated value for the current time period and all of the previous time periods within a larger time interval. For example, you might derive the year-to-date total for each month.
Note: This derived item is not available if your data source does not contain a date data item that includes the year.
Year over Year Growth
Displays the percentage difference between the current time period and an equivalent time period from the previous year. For example, you might derive the difference in sales between the current month and the same month of the previous year.
Note: This derived item is not available if your data source does not contain a date data item that includes the year.
Year to Date
Displays the aggregated value for the current time period and all of the previous time periods within the year. For example, you might derive the year-to-date total for each month.
The year-to-date calculation subsets the data for each year using today’s date (where today is evaluated each time you view the exploration). To use all data for every period, use a Period to Date item or edit the expression for the derived item.
Note: This derived item is not available if your data source does not contain a date data item that includes the year.
Year to Date Growth
Displays the percentage difference between the year-to-date value for the current time period and the year-to-date value for the same time period of the previous year. For example, you might derive the difference in year-to-date sales between the current month and the same month of the previous year.
The year-to-date calculation subsets the data for each year using today’s date (where today is evaluated each time you view the exploration). To use all data for every period, use a Period to Date item or edit the expression for the derived item.
Note: This derived item is not available if your data source does not contain a date data item that includes the year.
Note: The derived item types that aggregate values over time use periodic operators. For information about using periodic operators, see Periodic Operators.

Create a Derived Item for a Measure

You can create a derived item for a measure by using the Data pane or by selecting a measure in a visualization.
To create a derived item from the Data pane, right-click the data item that the derived item is based on, and then select Createthen selectitem-type. If you select a type that uses a date, select the date data item that is used for date calculations.
To create a derived item in a visualization, right-click a measure heading in the visualization, and then select Create and Addthen selectitem-type. If you select a type that uses a date, select the date data item that is used for date calculations. The derived item is added to the visualization automatically.
For a list of item types, see Derived Item Types for Measures.
The new derived item appears in the Data pane.

Create a Derived Item for a Category

To create a derived item for a category from the Data pane, right-click the category, and then select Createthen selectitem-type.
The new derived item appears in the Data pane.

Support for Derived Items

Derived items can be used in the following visualization types:

Explore Distinct Values in a Visualization

For a bar chart, line chart, treemap, or geo map that contains a distinct count derived item, you can explore the distinct values that are associated with a data point in the visualization. To explore the distinct values, right-click on a data point in the visualization, and then select Explore Distinct Values. When you explore the distinct values, a new bar chart is created. The new bar chart displays the values and the frequency of each value.
For example, you might use a distinct count derived item to create a bar chart that displays the number of cities where each product line is produced.
Then, you might want to explore the distinct values for City where Promotional items are produced. By right-clicking on the bar for Promotional, and then selecting Explore Distinct Values, you get a new bar chart that contains the cities where promotional items are produced and the frequency of data for each city.