# GetExcludedCount - chart function

GetExcludedCount() finds the number of excluded distinct values in the identified field. Only excluded (dark gray) fields are counted. Alternative (light gray) and selected excluded (dark gray with check mark) values are not counted.

Syntax:

GetExcludedCount (field\_name)

Return data type: integer

Information note

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 application 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

| Arguments | 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](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/FieldFunctions/getstatecounts.htm) | Using GetStateCounts(), you can combine the calculation of the following counts using a single function call:<br><br><ul> <li> <p>Count of selected included values.</p> </li> <li> <p>Count of possible values.</p> </li> <li> <p>Count of alternative values.</p> </li> <li> <p>Count of excluded values, not including alternative and selected excluded values.</p> </li> <li> <p>Count of selected excluded values.</p> </li> </ul> |
| [GetSelectedCount - chart function](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/FieldFunctions/getselectedcount.htm) | Returns the count of selected included values. |
| [GetPossibleCount - chart function](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/FieldFunctions/getpossiblecount.htm) | Returns the count of possible values. |
| [GetAlternativeCount - chart function](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/FieldFunctions/getalternativecount.htm) | Returns the count of alternative values. |

Example: Chart expressions

| Example | Result |
| --- | --- |
| `GetExcludedCount( Initials )` | Returns `0` if no selection has been made. If a selection has been made then the value returned is the number of excluded values in `Initials` with dark gray color. |

## Example - GetExcludedCount fundamentals

[](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/FieldFunctions/getexcludedcount.htm)

#### Chart expression

Overview

This example shows how values that are returned by the `GetExcludedCount` function change as additional selections are made.

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

1. Load the data and open a sheet. Create a new filter pane and add these fields as dimensions:

   * `FirstName`

   * `LastName`

   * `Initials`

2. Create three KPI objects and add the following expressions as measures, one per KPI object:

   1. `=GetExcludedCount(FirstName)`, to count the number of excluded values in the `FirstName` field.

   2. `=GetExcludedCount(Initials)`, to count the number of excluded values in the `Initials` field.

   3. `=GetExcludedCount(LastName)`, to count the number of excluded values in the `LastName` field.

   Your sheet will look like the following image because no values have been selected. The `GetExcludedCount` function returns `0` for each KPI.

   ![A sheet with filter panes for First Name, Initials, and Last Name, as well as three KPI objects that each return a count of any excluded values in these fields. ](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Resources/Images/scripting-functions-GetExclCnt-example.png)

3. Click Edit sheet to switch to analysis mode, and then make the following selections in order:

   Filter selections and results in analysis mode

   | Filter pane selection | KPI results |
   | --- | --- |
   | Select `John` in the `FirstName` filter. | <ul> <li><code>GetExcludedCount (Initials)</code> returns a value of <code>5</code> because there are <code>5</code> excluded values in <code>Initials</code> with dark gray color. The <code>JA</code> value remains white as it is associated with the selection <code>John</code> in <code>FirstName</code>.                                                 </li> <li><code>GetExcludedCount (LastName)</code> returns a value of <code>5</code> because there are <code>5</code> excluded values in <code>LastName</code> with dark gray color. The <code>Anderson</code> value remains white as it is associated with the selection <code>John</code> in <code>FirstName</code>.                                                </li> </ul> |
   | Select `John` and `Peter` in the `FirstName` filter. | <ul> <li><code>GetExcludedCount (Initials)</code> returns a value of <code>3</code> because there are <code>3</code> excluded values in <code>Initials</code> with dark gray color.                                                 </li> <li><code>GetExcludedCount (LastName)</code> returns a value of <code>3</code> because there are <code>3</code> excluded values in <code>LastName</code> with dark gray color.                                                </li> </ul> |
   | With `John` and `Peter` selected, select `Franc` in the `LastName` filter. | <ul> <li><code>GetExcludedCount (FirstName)</code> returns a value of <code>3</code> because there are <code>3</code> excluded values in <code>FirstName</code> with dark gray color and no selection check mark. <code>The GetExcludedCount</code> function counts only excluded values. Alternative and selected excluded values are not included in the count.  <code>John</code> is a selected excluded value.                                                </li> <li><code>GetExcludedCount (Initials)</code> returns a value of <code>5</code> because there are <code>5</code> excluded values in <code>Initials</code> with dark gray color.                                                </li> <li><code>GetExcludedCount (LastName)</code> returns a value of <code>3</code> because there are <code>3</code> excluded values in <code>LastName</code> with dark gray color. The 2 light gray color values, <code>Anderson</code> and <code>Devonshire</code>, are alternative values.                                                </li> </ul> |
   | Keep the previous selections, and then in the `LastName` filter, select `Anderson`. | <ul> <li><code>GetExcludedCount (FirstName)</code> returns a value of <code>3</code> because there are <code>3</code> excluded values in <code>FirstName</code> with dark gray color.                                                   </li> <li><code>GetExcludedCount (Initials)</code> returns a value of <code>4</code> because there are <code>4</code> excluded values in <code>Initials</code> with dark gray color.                                                </li> <li><code>GetExcludedCount (LastName)</code> returns a value of <code>3</code> because there are <code>3</code> excluded values in <code>LastName</code> with dark gray color.                                                 </li> </ul> |

You can see the final results of the filter pane selections in the following image.

KPI results of selecting the first names, John and Peter, and last names, Franc and Anderson

KPI results of selecting the first names, John and Peter, and last names, Franc and Anderson

KPI results of selecting the first names, JohnJohn and PeterPeter, and last names, FrancFranc and AndersonAnderson ![A sheet with selections on the First Name and Last Name fields, and the corresponding KPI results from these selections. ](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Resources/Images/scripting-functions-GetExclCnt-example2.png)

## Example - GetExcludedCount scenario

[](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/FieldFunctions/getexcludedcount.htm)

#### Chart expression

Overview

A sales manager uses the `GetExcludedCount` function to create a KPI chart to analyze products sold by salesperson.

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:

  * `Salesperson`

  * `Product`

  * `SalesAmount`

Load script

```
Example:
LOAD * INLINE [
Salesperson, Product, SalesAmount
John, Laptops, 1000
Alice, Smartphones, 800
Bob, Laptops, 1200
Carol, Tablets, 700
Dave, Tablets, 900
];
```

Results

1. Load the data and open a sheet. Create a new filter pane and add these fields as dimensions:

   * `Salesperson`

   * `Product`

2. Create a new KPI object and add the following measure:

   * `=GetExcludedCount(Product)`, to find the number of excluded values in the `Product` field.

3. Click Edit sheet to switch to analysis mode, and then select `Alice` from the `Salesperson` filter.

   The `GetExcludedCount` function returns a value of `2` because there are `2` unique and excluded (dark gray) values in the `Product` field.

   ![A sheet with filter panes for First Name, Initials, and Last Name, as well as three KPI objects that each return a count of any excluded values in these fields. ](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Resources/Images/scripting-functions-GetExclCnt-example3.png)

The results show how you can use the `GetExcludedCount` function to analyze the data. The salesperson `Alice` has not sold any items in 2 of the 3 product groups, `Laptop` and `Tablet` products.