The TEXTLIST aggregation function returns a collection of text values as a comma-separated value. The values returned are based on mapping from a source module.

For example, you can use the TEXTLIST function to return a list of customers for each region.

Values to list[TEXTLIST: Mapping, TEXTLIST: Mapping 2, etc.]

ArgumentData typeDescription
Values to listTextThe values to return as comma-separated text values based on the Mapping argument.
MappingList, date, time period

The mapping that determines which values to return as comma-separated text values.

Each instance of this argument must be a dimension present in the Values to sum argument.

This argument can be repeated to provide multiple mappings.

The TEXTLIST function returns a text result. The result line item must share all dimensions used for the Mapping argument.

You cannot use the TEXTLIST aggregation function in Polaris.

In the Classic engine, you can.

Customer Sales.Product Text[TEXTLIST: Customer Sales.Customers, TEXTLIST: Customer Sales.Sales Period]

In this example, each text value from the Product Text line item is returned as a comma separated value for each unique combination of Customer and Sales Period.

You can reference the Users list with the TEXTLIST function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

The dimensions of the Mapping argument must also be dimensions of the Values to list argument.

The TEXTLIST aggregation function can only be used with values of the text data type. If you need to list values with the list data type, you can use the NAME function to convert list values to text.

The TEXTLIST aggregation function returns duplicates if a text value occurs more than once in the cells chosen through the Mapping argument. The TEXTLIST calculation function enables you to only return each text value once, or to change the separator between text values.

The TEXTLIST aggregation function has a built in limit of 10,000 characters to prevent enormous text strings being created. At 10,000 characters, TEXTLIST stops appending to the list and adds an ellipsis that indicates more items. The ellipsis is after the 10,000 character limit, not included within it. The TEXTLIST function may truncate the final word listed if it exceeds 10,000 characters.

CONCATENATE

The module below is the Transactions module, which contains the data that the other examples aggregate. In this module, the Transactions list is on rows. Several line items are on columns, which contain:

  • A text value of the product sold
  • A list value for the customer sold to
  • A time period value for when the transaction occurred

Product TextCustomerDate
Transaction 01ApplesCustomer AJan 21
Transaction 02BananasCustomer BJan 21
Transaction 03GrapefruitCustomer AJan 21
Transaction 04OrangesCustomer CFeb 21
Transaction 05ApplesCustomer DMar 21
Transaction 06GrapefruitCustomer BMar 21
Transaction 07OrangesCustomer BApr 21
Transaction 08BananasCustomer CApr 21
Transaction 09GrapefruitCustomer AMay 21
Transaction 10OrangesCustomer BJul 21
Transaction 11BananasCustomer AJul 21
Transaction 12OrangesCustomer AAug 21
Transaction 13BananasCustomer DSep 21
Transaction 14ApplesCustomer BSep 21
Transaction 15GrapefruitCustomer DOct 21
Transaction 16ApplesCustomer DOct 21
Transaction 17OrangesCustomer CNov 21
Transaction 18GrapefruitCustomer CNov 21
Transaction 19ApplesCustomer CDec 21
Transaction 20BananasCustomer DDec 21

In this example, the Customer list is on columns. On rows, a line item contains a formula that uses TEXTLIST to list each product sold to each customer.


Customer ACustomer BCustomer CCustomer D

Products sold by customer

Transactions.Product Text[TEXTLIST: Transactions.Customer]

Apples, Grapefruit, Grapefruit, Bananas, OrangesBananas, Grapefruit, Oranges, Oranges, ApplesOranges, Bananas, Oranges, Grapefruit, ApplesApples, Bananas, Grapefruit, Apples, Bananas

In this example, the Time is on columns. On rows, a line item contains a formula that uses TEXTLIST to list each product sold in each time period.


Jan 21Feb 21Mar 21Apr 21May 21Jul 21Aug 21Sep 21Oct 21Nov 21Dec 21

Products sold in period

Transactions. Product Text[TEXTLIST: Transactions.Date]

Apples, Bananas, GrapefruitOrangesApples, GrapefruitOranges, BananasGrapefruitOranges, BananasOrangesBananas, ApplesGrapefruit, ApplesOranges, GrapefruitApples, Bananas

In this example, the Customer list is on columns, and Time on rows.

Line items are on pages, and the selected line item contains this formula:

Transactions.Product Text[TEXTLIST:Transactions.Customer, TEXTLIST: Transactions.Date]

The formula lists the products sold for each combination of customer and time period.


Customer ACustomer BCustomer CCustomer D
Jan 21Apples, GrapefruitBananas

Feb 21

Oranges
Mar 21
Grapefruit
Apples
Apr 21
OrangesBananas
May 21Grapefruit


Jul 21BananasOranges

Aug 21Oranges


Sep 21
Apples
Bananas
Oct 21


Grapefruit, Apples
Nov 21

Oranges, Grapefruit
Dec 21

ApplesBananas