Using the Intersection operator ( ^ )
Applies to:
- Velixo Classic
- Velixo NX
- Every ERP
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 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:
As a result, the matching subaccount values would be: 100-1, 200-1, 300-1, 400-1, 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 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,";")&"-???"), |