GetAlternativeCount - chart function

GetAlternativeCount() is used to find the number of alternative (light gray) values in the identified field.

Syntax:

GetAlternativeCount (field_name)

Return data type: integer

The colors used in the selection bar, and for each selection state, can be modified with a custom theme. If you are working with an app that uses a custom theme, you might notice that your selections do not display with the same colors that are described in this help topic.

Arguments
Argument	Description
field_name	The field containing the range of data to be measured.

The following table lists other functions that are related to this function.

Related functions
Function	Interaction
GetStateCounts - chart function	Using GetStateCounts(), you can combine the calculation of the following counts using a single function call: Count of selected included values. Count of possible values. Count of alternative values. Count of excluded values, not including alternative and selected excluded values. Count of selected excluded values.
GetSelectedCount - chart function	Returns the count of selected included values.
GetPossibleCount - chart function	Returns the count of possible values.
GetAlternativeCount - chart function	Returns the count of excluded values, not including alternative and selected excluded values.

Example: Chart expressions
Example	Result
GetAlternativeCount ([First name])	Returns 0 if no selection has been made on the First Name field. If selections have been made, then the value returned is the total number of distinct available values less the number of values selected.

Example - GetAlternativeCount fundamentals

Overview

Open the Data load editor and add the load script below to a new section.

The load script contains:

A dataset which is loaded into a data table called Example.
The following fields in the data table:
- FirstName
- LastName
- Initials
- HasCellphone

Load script

Example:
LOAD * inline [
FirstName|LastName|Initials|HasCellphone
John|Anderson|JA|Yes
Sue|Brown|SB|Yes
Mark|Carr|MC|No
Peter|Devonshire|PD|No
Jane|Elliot|JE|Yes
Peter|Franc|PF|Yes ] (delimiter is '|');

Results

Load the data and open a sheet. Create a new filter pane and add this field as a dimension:

FirstName

Add a KPI, and then create the following measure:

=GetAlternativeCount (FirstName), to find the number of alternative values in the FirstName field.

The KPI with the GetAlternativeCount function appears with a value of 0. If you select Jane from the FirstName filter pane, the KPI returns the value 4 because there are four unique and excluded (gray) values. Now select Mark, so both Jane and Mark are selected. The KPI value for the GetAlternativeCount measure returns 3 because there are 3 unique and excluded values, John, Peter, and Sue.

The filter pane shows two selections so the GetAlternativeCount KPI returns 3, because there are 3 alternative values that are unique and excluded from the selection. — KPI results showing 3 unique and excluded values after selecting Jane and Mark from the FirstName filter pane

You can see how the values returned by the GetAlternativeCount function change as additional selections are made.

Example - GetAlternativeCount scenario

Overview

Open the Data load editor and add the load script below to a new section.

The load script contains:

A dataset which is loaded into a data table called CustomerTypeData.
The following fields in the data table:
- CustomerID
- CustomerType

Load script

CustomerTypeData:
LOAD * INLINE [
CustomerID, CustomerType
C001, Retail
C002, Wholesale
C003, Distributor
C004, Retail
C005, Wholesale
];

Results

Load the data and open a sheet. Create a new filter pane and add this field as a dimension:

CustomerType

Add a KPI, and then create the following measure:

=GetAlternativeCount (CustomerType), to find the number of alternative values in the CustomerType field.

The KPI with the GetAlternativeCount function appears with a value of 0 because no values are selected in the CustomerType field.

Select Retail from the CustomerType filter pane. The GetAlternativeCount returns the value 2 because there are two unique and excluded (light gray) values in the CustomerType field. .

The filter pane shows one selection so the GetAlternativeCount KPI returns 2, because there are 2 alternative values that are unique and excluded from the selection. — KPI results showing 2 unique and excluded values after selecting Retail from the CustomerType filter pane

The results show that although there are multiple values, the GetAlternativeCount function displays only the unique count of the items not selected.

Example - GetAlternativeCount scenario with available values

Overview

This example builds on the previous scenario. The dataset includes a second data table to demonstrate how selections in each field change the results returned by the GetAlternativeCount function.

Open the Data load editor and add the load script below to a new section.

The load script contains:

A dataset which is loaded into the following 2 inline data tables and corresponding fields:
- CustomerTypeData: Includes the fields CustomerID and CustomerType.
- CustomerSegment: Includes the fields CustomerType and Segment.

Load script

CustomerTypeData:
LOAD * INLINE [
CustomerID, CustomerType
C001, Retail
C002, Wholesale
C003, Distributor
C004, Retail
C005, Wholesale
];

CustomerSegment:
Load * Inline [
CustomerType, Segment
Retail, Fashion
Retail, Home
Wholesale, Fashion
Wholesale, Drinks
Distributor, Food
];

Results

Load the data and open a sheet. Create a new filter pane and add these fields as dimensions:
- CustomerType
- Segment
Add a KPI, and then create the following measure:
- =GetAlternativeCount (CustomerType), to find the number of alternative values in the CustomerType field.
  
  The KPI with the GetAlternativeCount function appears with a value of 0 because no values are selected in the CustomerType or Segment fields.
Select Drinks and Fashion from the Segment filter. The KPI still returns 0 because no value is selected in the CustomerType field.

The GetAlternativeCount KPI returns 0 because no value is selected in the CustomerType filter. — KPI results show zero unique and excluded values after selections in the Segment filter

Select Retail in the CustomerType field and keep the selections in the Segment filter.

The GetAlternativeCount function now returns 1 because there is only one unique and excluded (light gray) value in the CustomerType field, Wholesale.

The GetAlternativeCount KPI returns 1 because there is only one alternative value that is unique and excluded from the selections in the CustomerType and Segment filters. — KPI results show there is 1 unique and excluded value after selections in the CustomerType and Segment filters

Although two values, Wholesale and Distributor, are not selected in the CustomerType field (these appear light gray and dark gray), only one value, Wholesale, is available to select in CustomerType due to selections in the Segment filter.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here