Filter
Top Products
116
Chapter 7
You can also use a number of counting functions to o btain counts of values that meet specific
criteria , eve
n when those values are stored in multiple fields. For exa mple, to count the number of
cards that have been held for more than five years:
count_greater_than(5, ['cardtenure' 'card2tenure' 'card3tenure'])
To count null values across the same set of fields:
count_nulls(['cardtenure' 'card2tenure' 'card3tenure'])
Note that this example counts the number of cards being held, not the number of people holding
them. For more information, see the topic Comparison Functions in Chapter 8 on p. 135.
To count the number of times a specified value occurs across multiple fields, you can use the
count_equal function. The following example counts the number of fields in the list that contain
the value Y.
count_equal("Y",[Answer1, Answer2, Answer3])
Given the following values for the fields in the list, the function returns the results for the value Y
as shown.
Answer1 Answer2 Answer3 Count
Y N Y
2
Y N N
1
Numeric Functions
You can obtain statistics across multiple fields using the sum_n, mean_n, and sdev_n
functions—for example:
sum_n(['card1bal' 'card2bal''card3bal'])
mean_n(['card1bal' 'card2bal''card3bal'])
For more information , see the topic Numeric Functions in Chapt er 8 on p. 138.
Generating Lists of Fields
When usin g any of the functions that accept a list of fields as input, the special functions
@FIELDS_BETWEEN(start, end) and @FIELDS_MATCHING(pattern) can be used as input. For
example, assuming the order of fields is as shown in the sum_n example earlier, the following
would be equivalent:
sum_n(@FIELDS_BETWEEN(card1bal, card3bal))
Alternatively, to count the number of null va lues across all fields beginning with “card”:
count_nulls(@FIELDS_MATCHING('card*'))
For more i nformation, see the topic Special Fields in C hapter 8 on p. 157.