Applies to:

• Velixo Classic
• Velixo NX
• Every ERP

TABLE OF CONTENTS

• Overview
• Add multiple entities with a semi-colon ( ; ) or comma ( , )
• Using a range ( START:END )
• Wildcard operator ( ? )
• Everything operator ( * )
• Subtractor operator ( - )
  • Subtracting multiple entities
• Intersection operator ( ^ )

Overview

When creating functions to query our data, we may want to use parameters which include ranges, use wildcards, exclude certain values, etc.

There are several methods that can help us do that:

• creating a list - separating values with a semi-column ( ; ) or comma ( , )

• using a range of values ( START:END )

• using a wildcard ( ? ) - e.g., all accounts starting with 4: 4????

• including all values for the parameter ( * )

• subtracting specific values ( - )

• filtering a range using intersection ( ^ )

This article explores each of these methods and provides a usage example for each.

Add multiple entities with a semi-colon ( ; ) or comma ( , )

To include multiple entities in the parameters of a function, we can create a list of values and use a semi-colon to separate each one.  The use of commas is also supported but not recommended since it is also the thousands separator in many locales.

In this example, the three accounts we want to include are separated by a semicolon.

Example: =ACCOUNTENDINGBALANCE("Demo", ACTUAL,,"10100;10200;10300",,,"09-2019")

Using a range ( START:END )

To include all entities of a range, we use a colon to denote that our values are the starting and ending values of the range.

In this example, the range is defined as starting with 10100 and ending with 11500.

Example: 

=ACCOUNTENDINGBALANCE("Demo", ACTUAL,,"10100:11500",,,"09-2019")

We can also combine semicolon lists and ranges. In this example, we are retrieving the ending balance for accounts 10100 through 11300 and also including account 10600

Example: 

=ACCOUNTENDINGBALANCE("Demo", "ACTUAL",, "10100:11300;10600",,,"09-2019")

Wildcard operator ( ? )

To add all entities that match a pattern, we use a question mark (?) in the parameter of a function, to denote "any character". This operator can be used at any point in the pattern, such as "10???", "??999", and "1???0".

In this example, three question marks follow the first two digits of the account number. The result will include only those accounts that begin with 10.

Example: =ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"10???",,,"09-2019")

In this example, the two question marks at the beginning of the account number will result in including only those accounts that end with 999.

Example: =ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"??999",,,"09-2019")

Everything operator ( * )

To add all available entities in the parameter of a function, we can use an asterisk (*).

In the example below, the asterisk specifies that every branch will be included. This is effectively the same as leaving the parameter empty.

Example: =ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"10100",,"*","09-2019")

Let's say, for example, that we want to calculate the balance of an account for every branch except a few branches. We can't use the ? wildcard for our branch (unless the names all our branch identifiers are of the same length), and it's tedious to write out a long list of the accounts. By using the * operator, we can easily include or exclude the branches we want.

Example: =ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"10100",,"*;-ONEBRANCH","09-2019")

Subtractor operator ( - )

We can use the Subtractor operator with the Account, Subaccount, or Branch parameters to exclude certain values from a range in these GL/Accounting functions:

• ACCOUNTTOTALDEBITS

• ACCOUNTTOTALCREDITS

• ACCOUNTTURNOVER

• ACCOUNTBEGINNINGBALANCE

• ACCOUNTENDINGBALANCE

This operator can also be put to use in the following parameters found in many of the Project Module Functions:

• AccountGroup

• CostCode

• Project

• InventoryItem

• Branch

Subtracting multiple entities

Often, it is inefficient to list all the branches to include. It is possible to use the Subtractor operator to subtract many entities from a range.

In this example, we use the Subtractor to exclude the SERVEAST, SERVWEST, and PRODRETAIL branches:

Example: =ACCOUNTENDINGBALANCE("Demo", "ACTUAL",,"49300",,"*;-SERVEAST;-SERVWEST;-PRODRETAIL","12-2019")

Another example where this would be useful is in a project report where we want to calculate expenses and exclude specific item codes, as with"*;-TRAVEL".

Intersection operator ( ^ )

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

Imagine the following scenario, where each subaccount has two segments:

• Department code, ranging from 100 to 500:

  • 100 - Purchasing

  • 200 - Production

  • 300 - Marketing & Sales

  • 400 - R&D

  • 500 - HR & Administrative

• 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 iterates 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). 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 this expression:

• The first part (100-1:400-1) still allows subaccounts 100-1, 100-2, …, through 400-3 - not what we want per se.

• 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 what we want per se (it includes HR & Administrative).

• 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 C and range D.