Set qualification - Microstrategy

A set qualification allows you to create a set based on either a metric qualification or a relationship qualification.

Set qualification: metric qualification

Metric qualifiers enable you to restrict metric values based on value, rank, or rank percentage. Metric qualifiers restrict the amount of data used to calculate the metrics on a report. For example, a store manager might want to see sales numbers for products whose current inventory levels fall below a certain level. This report will not necessarily display the inventory figures for those products.

Output level

The output level specifies the level at which the metric is calculated for the qualification. For example, if the metric qualification is Sales > 1000, Sales could mean sales per day, month, year, store, region, and so on. Creating a set qualification with an output level of store is equivalent to having a fixed list of stores, if you knew which ones met the metric qualification, in a simple attribute qualification. However, the list of stores in the qualification is generated dynamically.

The output level can be specified in several ways.

  • An attribute list allows you to specify the exact set of attributes (such as day, month, or year) to use as the output level.
  • Report level means that the output level is defined by the level of the report that contains the metric qualification. For example, if the lowest level of the report is year and the output level is set to report level, the metric is calculated for the year.
  • Metric level means that the output level is defined by the level, or dimensionality, of the metric itself, regardless of the level of the report.
  • The None selected option calculates the results at the report level if any of the following is true:
    • The metric is a compound metric.
    • The metric’s dimensionality is set to report level.
    • The metric’s dimensionality is set to nothing.

Otherwise, the metric's dimensionality is used.

If you do not select an output level, the None selected option is used by default.

Break by

This advanced function of a metric qualification allows you to choose the attribute level at which to restart counting rank or percent values for a metric. This level must be greater than or equal to the level of aggregation for the metric itself, as shown in the following example.

Given the following data:

Break by

If you specify “Break by Market,” the ranking counter is reset for each market (in descending order).

Break by Market,

If you specify “Break By Region,” the ranking counter is reset for each region (in this example, as there is only one region, the counter is not reset).

Break by Market,

Merge attribute qualifications

The Advanced button allows you to specify whether existing attribute qualifications should be merged into the calculation of the metric qualification. By default, this option is selected, combining the qualifications.

A metric qualification is contained in a separate pass of SQL, creating a temporary table or “mini-report.” If the qualifications are merged, attribute qualifications are added to that pass of SQL. If they are not merged, the attribute qualifications are not included in the metric qualification. They instead appear in the main SQL pass.

For example, a report shows revenue by region. The report 2002 and the metric qualification of revenue over $1 million. If the default is kept, the qualifications are merged. Only 2002 revenue is considered when the metric checks for revenue over $1 million. The report results are:

Merge attribute qualifications

In contrast, if the qualifications are not merged, revenue is calculated for all time before the metric qualification is evaluated. However, only revenue from the year 2002 is displayed on the report. As shown in the following sample,regions are included that do not have $1 million of revenue in 2002, but do have $1 million of revenue across time.

Merge attribute qualifications

Besides affecting the report results, merging the qualifications reduces the amount of data a calculation must process.

Metric-to-metric comparison

Metric-to-metric comparisons allow you to create reports that dynamically compare the values of two metrics. For example, you can create a report that restricts the data torevenue greater than last quarter’s revenue.

Create a report that displays the revenue for Call centers Atlanta, San Diego, and Miami for each quarter during the year 2002. To do this, create one filter that includes the call centers Atlanta, San Diego and Miami and another filter that includes the year 2002. For step-by-step directions on creating a filter, see the online help.

When you execute the report, it looks like the following:

Metric-to-metric comparison

Now, create a revenue metric that calculates the revenue for the previous quarter and save it as RevenueLastQuartermetric. Create a metric-to-metric comparison filter that uses the Revenue metric. Choose the Function as Metric Value,and Operator as Greater than. Choose the Value as Metric and browse to select the newly created metric Revenue Last Quarter. Save the filter LastQuarter. The report,when re-executed with the LastQuarter filter,now looks like the following:

Metric-to-metric comparison

Note that only those revenues whose values are greater than the revenues of the previous quarter are displayed on the report.

Set qualification: relationship qualification

Relationship qualification allows you to create a link between two attributes and place a filter on that relationship. It allows you to create a set of elements from an attribute based on its relationship with another attribute. For example, relationship filtering allows you to create a report that shows you all the stores selling Nike shoes in the Washington, DCarea or all customers who have checking accounts but no saving accounts.

You can create relationship filters using either Set qualification or Advanced qualification in the Filter Editor. Set qualification provides an interface to guide you through the process, while you must enter commands in Advanced Qualification. The syntax for the Advanced qualification is described in Advanced qualification: relationship filters.

You have the following options while creating a relationship qualification:

  • Output level is the level at which the set is calculated. You can select the attribute(s) for the output level.
  • Filter Qualification defines the input filtering criteria, that is, the relationship on which to qualify. You can select an existing filter or create a new filter.
  • Relate output level and filter qualification by is the relation between the attributes in the output level and filter qualification. The relation can be a fact, a table, or an empty filter. If the relationship is left empty, the schema is used to select the appropriate table.

For example, to create a report that shows all the stores selling Nike shoes in the Washington, DC area, you need to set the output level to Stores, the filter qualification to Nike shoes and Region, and the relation to the fact Sales.

Metric qualification prompt

A metric qualification prompt allows you to select a function, or an operator, or specify the value for a metric, when you run a report. You can create the following types of metric qualification prompts:

  • Qualify on a metric prompt allows you to qualify on a metric. You can choose a metric by specifying a single metric to use when the report is run or by specifying a search object to restrict the list of metrics from which you can choose a metric, when a report is run.
  • Metric Object prompt allows you to select one or more metrics that meet specific criteria when a report is run. For example, you could use a search to return a list of all metrics that contain a certain fact. From that list of metrics, you can then choose the metrics that you want to see on the report.
  • Value prompt allows you to select a single value on which to qualify, such as a date, a specific number, or a specific text string.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Microstrategy Topics