Using the Intersection operator ( ^ )
Created by Harry Lewis, Modified on Wed, 21 Aug at 9:58 AM by Harry Lewis
Applies to:
 Velixo Classic
 Velixo NX
 Every ERP
TABLE OF CONTENTS
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:
An example of a full subaccount value would be 3002, denoting Marketing & Sales in EMEA. Suppose now that in a Velixo formula, we want to aggregate data for all productrelated departments, 100 through 400, but only within a single geographical area, 1. We might start to write a range expression: 1001:4001 However, ranges always iterate through all subaccount segments (in this case, including the other geographical areas into the results: 1001, 1002, 1003, 2001, 2002, 2003, …, 4001, 4002, 4003). 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: =1001:4001^???1 In that expression:
As a result, the matching subaccount values would be: 1001, 2001, 3001, 4001, which is precisely what we wanted.

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 XXXYYZZZAAA. 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,";")&"???"), 
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article