Qlik Sense Field Functions – Count, Field & Selection Functions

1. Objective

Earlier we discussed Qlik Sense Day Numbering Functions. Today, we will see Qlik Sense Field Functions. The functions are categorized into two categories, Count functions and field, and selection functions. Let us start with learning and understanding them better.

So, let’s start Qlik Sense Field Functions tutorial.

Qlik Sense Field Functions - Count, Field and Selection

Qlik Sense Field Functions – Count, Field and Selection

Have a look at Qlik Sense Capabilities

2. Qlik Sense Field Functions

The field functions are applied on the fields of the chart. For every time we load data in Qlik Sense’s memory, an internal table is created. Such tables have data fields(columns) and rows. The field functions help in applying operations on the fields and managing them. These functions can return both integers or string values depending upon what is asked. Also, we know, in all the chart types, we can make selections upon fields and field values. Some of the field functions are used upon these selections as well.

i. Count functions

a. GetSelectedCount() function

This function returns the count or number of data values which are currently selected i.e. are green.

Syntax:

GetSelectedCount(field_name[,include_excluded[,state_name]])

Where, field_name is the name of the field you want to count the selections in.

Include_excluded parameter if set to True, includes all the excluded fields which got excluded because of the selection made. If you do not mention anything about this parameter in the expression, then it is assumed as False and excluded values are not counted.

State_name is the name of an alternate state that you might set for a specific visualization. If you specify this name in this parameter, then field values will be counted from it.

For example, suppose we have a script in which there are fields named First name, Last name, and Initials.

Do you know about Qlik Sense Null Functions

Names:
LOAD * inline [
"First name"|"Last name"|”Initials”
Rajiv|Mehra|RM
Smriti|Sharma|SS
Mohak|Verma|MV
Parth|Kashyap|PK
John|Desilva|JD
Ketan|Bajaj|KB ] (delimiter is '|');

If Rajiv is selected in First name, then the function given below will return,

GetSelectedCount ([First name])

Returns 1 as one value is selected in First name.

Now, given that Rajiv is selected in First name, if we try to count the selected values in the field Initials, then

GetSelectedCount ([Initials])

0 will return, as no values are selected in Initials.

b. GetAlternativeCount() function

This function returns the number of values selected as alternative values which is shown in light gray.

Let’s revise Qlik Sense Mapping Functions

Syntax:

GetAlternativeCount(field_name)

Where the field_name is the name of the field in which the values are in the alternative state.

For example, if we make a selection pane from the fields given in the code below,

Names:
LOAD * inline [
"First name"|"Last name"|”Initials”
Rajiv|Mehra|RM
Smriti|Sharma|SS
Mohak|Verma|MV
Parth|Kashyap|PK
John|Desilva|JD
Ketan|Bajaj|KB ] (delimiter is '|');

Then, if from the First name field, Rajiv is selected the expression,

GetAlternativeCount([First name])

Returns, 5 as except for Rajiv, which will be in green, all the other values will be in light gray i.e. in alternative state.

c. GetPossibleCount() function

This function returns the count of values under the possible state i.e. those which are shown in white cells. Such values are the possible outcome of a section made in another field.

Syntax:

GetPossibleCount(field_name)

For instance, we have two fields named ‘Product Type’ and ‘Item’. If you select a value ‘Health & Beauty’ from the field Product Type then in the field Item three values will be counted as possible values i.e. the values which Health & Beauty is associated with. The three values are, Aloe Vera gel, Body lotion, Face pack. So, the expression, GetPossibleCount(Item) will return 3.

Have a look at Qlik Sense Logical Functions

d. GetExcludedCount() function

This function returns the count of excluded values, i.e. such values which are not associated with the current selection. The values in the excluded state are shown in dark gray cells.

Syntax:

GetExcludedCount(field_name)

For instance, in the code given below,

Names:
LOAD * inline [
"First name"|"Last name"|”Initials”
Rajiv|Mehra|RM
Smriti|Sharma|SS
Mohak|Verma|MV
Parth|Kashyap|PK
John|Desilva|JD
Ketan|Bajaj|KB ] (delimiter is '|');

If Smriti is selected in the First name field then, GetExcludedCount(Initials) will return 5 as there are 5 values which are excluded and only one SS is associated and is in white cell.

And, if Smriti and John both were selected then, GetExcludedCount(Initials)will return 3 as the count of excluded values. If no selections are made in the selection panel, then 0 is returned.

e. GetNotSelectedCount() function

This function returns the count of not selected values in a field.

Syntax:

GetNotSelectedCount(fieldname[, includeexcluded=false])

Where the fieldname is the name of the field in which you wish to count the number of not selected values.

Do you know about Qlik Sense Pie Chart

Includeexcluded parameter if set to False, then the excluded values are not included in the counting. You can set it to True if you wish.

For instance,

GetNotSelectedCount(Country) will return the count of not selected values in the field Country.

ii. Field and Selection functions

a. GetCurrentSelections() function

This function returns all the currently selected values in one or more than one field. It returns a string value as a result.

Syntax:

GetCurrentSelections([record_sep [, tag_sep [, value_sep [, max_values [, state_name]]]]])

Where, record_sep is the separator that is put between fields or records. Usually it is set as <CR><LF> meaning a new line.

tag_sep is the separator put between the field names and the field values. By default, it is set at ‘:’.

value_sep is the separator used between field values. Such as ‘,’.

max_values sets the maximum number of field values that can be listed individually. The default value is set to 6.

From the state_name parameter, you can mention an alternate state of the visualization, if present.

Let’s revise Qlik Sense Scatter Plot

For instance, in the code given below,

Names:
LOAD * inline [
"First name"|"Last name"|”Initials”
Rajiv|Mehra|RM
Smriti|Sharma|SS
Mohak|Verma|MV
Parth|Kashyap|PK
John|Desilva|JD
Ketan|Bajaj|KB ] (delimiter is '|');

If Rajiv is selected from the field First name, then the expression GetCurrentSelections() will return the string ‘First name: Rajiv’.

And if, Rajiv, Ketan from the field First name and RM, KB from the field Initials. In this case, the expression GetCurrentSelections()will return,

‘First name: Rajiv, Ketan
Initials: RM, KB’

b. GetFieldSelections() function

The GetFieldSelections() function returns the values that are selected in a particular field. This function returns a string or text value.

Syntax:

GetFieldSelections(field_name [, value_sep [, max_values [, state_name]]])

Where, field_name is the name of the field from which you wish to get the selected values.

value_sep is the separator used between field values. Such as ‘,’.

max_values sets the maximum number of field values that can be listed individually. The default value is set to 6.

From the state_name parameter, you can mention an alternate state of the visualization, if present.

Let’s take a tour to Qlik Sense Mathematical Functions

For instance, if we make a filter pane having the three fields First name, Last name, Initials using the code given below,

Names:
LOAD * inline [
"First name"|"Last name"|”Initials”
Rajiv|Mehra|RM
Smriti|Sharma|SS
Mohak|Verma|MV
Parth|Kashyap|PK
John|Desilva|JD
Ketan|Bajaj|KB ] (delimiter is '|');

If three values MV, PK and KB are selected from the field Initials, then the expression GetFieldSelections([Initals]) will return ‘MV, PK, KB’.

So, this was all in Qlik Sense Field Functions. Hope you like our explanation.

3. Conclusion

Hence, these were all the important Qlik Sense field functions which are used to handle and count values under different selection states in a field or numerous fields. Still, if you have any questions related to Qlik Sense Field Functions.

See also – 

Qlik Sense Table Functions

Reference for Qlik Sense 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.