Skip to main content
Skip table of contents

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:


  • 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)





JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.