A unique count field (also known as count once) counts unique key members (customers, products, etc.) that occur in a set of records or meet test criteria. For a dimension member count (channels, brands, etc.), the field counts unique dimension members associated with key members in the records. Examples include number of customers who bought or brands that were sold. Unique counts belong to the causal data category.
Types of counts
Depending on how your organization sets this data up, it might use one or more of the following to calculate the counts. Your management should teach you the meaning of your counts and how they were determined. You can also place your cursor over the measure heading to see which of the following functions the field uses.
Netted unique counts (KeyCntNet or DimCntNet) – report the number of unique members that pass test conditions such as a value greater than zero for the specified measure. This is like the results obtained through an Exception analysis except that you can insert the data in more types of analyses. In addition, netted-unique-count measures can be incorporated into calculated fields.
Unique counts based on any record (KeyCntAnyRecord or DimCntAnyRecord) – report the number of members with at least one data record for the selected date range. This function only considers the currently selected data cube. If your organization uses more than one data cube, you should make sure the data cube you're concerned about is selected in the upper-left portion of the Context bar.
In a view focused on the Sales cube, a customer count of this type would show the number of customers with any sales record during the date range. The number would be the same regardless of which measures are in the view.
In a view focused on the Work Order cube, the same count field would show the number of customers with at least one work order record.
Unique counts based on a specific field (KeyCntByFld or DimCntByFld) – report the number of members with data for a specific measure determined by your management. The results will be the same regardless of which measures are in the view, and which data cube is selected.
If a customer count of this type is based on a Discounts field, then the count would show the number of customers with at least one record in which discounts are not zero. It would only look at Discounts, not sales units or any other measure, and would count a customer if it had a negative or positive Discount value.
Unique counts based on any non-zero field (KeyCntAnyNonZeroFld or DimCntAnyNonZeroFld) – report the number of members with non-zero (positive or negative) data for any of the measures in the current view, including the fields you can see, the sort order fields, and any hidden fields. This function only considers fields in the currently-selected data cube. This type of field can be beneficial because it lets you as a user determine exactly how UXT counts the members. However, you should have a good understanding of the current view in order to use this type of count. The following example helps show how the count results can change based on your view.
In a view focused on the Sales cube, a customer count of this type would show a different number depending on which fields are in the view.
1) If the view included Promoted Units only (and was sorted by Promoted Units), then the count would reflect the number of customers with values for promoted units. The count would not include all customers who bought, just those who received promoted merchandise.
2) If you inserted Sales Units, then the counts would change to reflect all customers with Promoted Units or Sales Units. In other words, the counts would go up.
3) If you inserted a measure from another cube (for example, work orders), then the counts would not change because the function ignores data from other cubes.
4) If you inserted a calculated measure (for example, Net Revenue), the counts would also include the customers who had data for the atomic components of the field (Gross Revenue or Discounts). In this situation, the count values may or may not change.
If the view only included data from the Sales cube (and was sorted by a field from this cube), but the Work Order cube was selected, then the view does not have a field to use for defining the counts. In this case, the count calculation will look at the entire Work Order cube and report all customers with any record.
Rules and considerations for unique counts
Calculated fields that include unique counts - As previously discussed, unique count fields that are defined by the KeyCntAnyRecord, DimCntAnyRecord, KeyCntAnyNonZeroFld, or DimCntAnyNonZeroFld function use the currently selected data cube to determine the count values. However, calculated fields that include these counts along with measures from a single data cube do not change based on the currently selected data cube. Instead, these types of calculated fields count members based on the data cube of other fields in the formula. Therefore, the results do not change when you switch the data cube although the results for the count field itself may change. The KeyCntByFld and DimCntByFld functions always use the specified measure and do not depend on the selected cube or cube of other fields in the formula. Calculated fields that include a count field and fields from multiple cubes are cross-calculated fields. These fields use the currently selected data cube to count key members. To see which data cube a calculated field uses to count key members, see the Cube Affinity section of the configuration report. This section lists the associated data cube. If no data cube is associated, then the calculated field uses the currently selected cube.
Totals and averages of key counts in comparative views - When the dimension used to group the data is for a different key than the count's key (for example, a customer count grouped by a product dimension such as brand), then the total for all members is not a simple addition calculation. This is because some of the counts might overlap each other. For example, a customer might have purchased two different brands and, therefore, be included in the customer count for both brands. To accommodate the overlap between dimension members, the total row looks at all key members to see how many have data for any of the dimension members in the view, and counts each key member only once. It would not be a total for all dimension members summed together. For this same reason, when you expand a dimension member (i.e. click the plus sign beside a dimension member to drill into its data), the counts won't necessarily add up to the expanded member's total. This doesn't mean the numbers are incorrect, just that they are calculated differently than additive data.
Totals and averages of dimension counts in comparative views - When the dimension used to group the data is different than the count's dimension (for example, a brand count grouped by the package dimension), then the total is not a simple addition calculation because counts can overlap each other. For example, a brand might occur in multiple packages.
Averages in time-series views - The totals cannot be arrived at by simply summing all of the days, weeks, months, or periods in the date range. Instead, the view shows a cumulative value for the whole date range.
Mix values - The percent of the total (i.e. mix) is only available when the view is grouped by a dimension of the same key as the count or, for dimension counts, by the same dimension as the count dimension. As discussed above, when the dimension is for a different key or dimension, the counts aren't additive. In other words, the counts might overlap each other, and therefore, don't add up meaningfully across dimension members. For this same reason, the mix values would not add up to 100%. This rule affects the following: Mix, Share Trend (Percent), and Mix columns inserted into Comparative views and key lists.
Conversion tables -
The test for netted unique counts is run after data has been converted with conversion tables (equivalents, calendar, and currency).
All other unique counts are based on measures before they have been converted with conversion tables. This could possibly result in the inclusion of a member in a count, even though the converted value is zero. For example, an equivalent table could convert volume to zero (e.g., in a promotion or give-away), but the count would still include the product or customer because it looks at the raw, unconverted data.