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:
=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
=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
=???-???-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:
=TEXTJOIN(";",TRUE,"???-???-"&TEXTSPLIT($A19,";")&"-???")&"^"&TEXTJOIN(";",TRUE,"???-"&TEXTSPLIT($B19,";")&"-??-???")

Using the LET function
This could also be accomplished in combination with the Excel LET
function:
=LET(StateFilter,TEXTJOIN(";",TRUE,"???-???-"&TEXTSPLIT($A21,";")&"-???"),
LineOfBusinessFilter,TEXTJOIN(";",TRUE,"???-"&TEXTSPLIT($B21,";")&"-??-???),
StateFilter&"^"&LineOfBusinessFilter)
