Using the Intersection operator ( ^ )

Overview

Sometimes we want to use a range for a parameter in a Velixo function, but really only want to include certain values from that range. To accomplish this, we can specify an intersection with the ^ operator.

Examples

Example 1 - Acumatica segmented subaccounts

Scenario

Let's assume we have subaccounts which are separated into two segments:

Segment 1 - Department code, ranging from 100 to 500:
- 100 - Purchasing
- 200 - Production
- 300 - Marketing & Sales
- 400 - R&D
- 500 - HR & Administrative

Segment 2 - Geographical area, ranging from 1 to 3:
- 1 - North & South America
- 2 - EMEA
- 3 - Asia-Pacific

An example of a full subaccount value would be 300-2, denoting Marketing & Sales in EMEA.

Suppose now that in a Velixo formula, we want to aggregate data for all product-related departments, 100 through 400, but only within a single geographical area, 1.

We might start to write a range expression: 100-1:400-1

However, ranges always iterate through all subaccount segments (in this case, including the other geographical areas into the results: 100-1, 100-2, 100-3, 200-1, 200-2, 200-3, …, 400-1, 400-2, 400-3). Obviously, that's not quite what we want

We can add the intersection operator "^" to specify an additional condition that every value in the range must adhere to:

CODE

=100-1:400-1^???-1

In that expression:

The first part (100-1:400-1) still allows subaccounts 100-1, 100-2, …, through 400-3
(not yet what we want)
The second part (???-1) allows any subaccount value that relates specifically to the North & South America geographical area: 100-1, 200-1, 300-1, 400-1, and 500-1
(also not exactly what we want (it includes the HR & Administrative departments)
The intersection operator allows only subaccount values that satisfy both parts of the expression.

As a result, the matching subaccount values would be: 100-1, 200-1, 300-1, 400-1, which is precisely what we wanted.

Effectively, the result is the intersection of two sets, {100-1, 100-2, …, 400-1, 400-2, 400-3 }, produced by the 100-1:400-1 range expression, and { 100-1, 200-1, 300-1, 400-1, 500-1 }, produced by the ???-1 range expression - Hence the "intersection" operator name.

Note that you can use more than one intersection operator, each of which will be applied as a logical "AND".

For example, in an expression like A^B^C^D, where A, B, C, and D are range expressions, a particular subaccount value will only be included in the results if it is already included in range A and range B and range C and range D.

Example 2 - Sage Intacct accounts

CODE

=SI.EXPANDACCOUNTRANGE("Sage","20000:39999^*;-???00)

Description

Returns all accounts between 20000 and 39999, except those ending in 00

Result

Example 3 - More Acumatica segmented subaccounts

Scenario

We have subaccounts which are separated into 4 segments in the form of XXX-YY-ZZZ-AAA.

We want to reference all subaccounts where the second segment is any of CA; FL; TX; or NY and the third segment is any of AAA; BBB; CCC; or DDD

CODE

=???-???-CA-???;???-???-FL-???;???-???-TX-???;???-???-NY-???^???-AAA-??-???;???-BBB-??-???;???-CCC-??-???;???-DDD-??-???

Automating

We could type the intersection filter by hand, or we can automate the process and build the filter by using Excel's TEXTJOIN and TEXTSPLIT functions:

CODE

=TEXTJOIN(";",TRUE,"???-???-"&TEXTSPLIT($A19,";")&"-???")&"^"&TEXTJOIN(";",TRUE,"???-"&TEXTSPLIT($B19,";")&"-??-???")

Using the LET function

This could also be accomplished in combination with the Excel LET function:

CODE

=LET(StateFilter,TEXTJOIN(";",TRUE,"???-???-"&TEXTSPLIT($A21,";")&"-???"),
LineOfBusinessFilter,TEXTJOIN(";",TRUE,"???-"&TEXTSPLIT($B21,";")&"-??-???),
StateFilter&"^"&LineOfBusinessFilter)