The 4 Uncommon Velixo Functions you need to knowVelixo has a range of powerful functions that extend the Dynamic Array functionality of MS Excel.
The Velixo Developers are continually responding to requests for new functions that can simplify the report designing process. The product is constantly being improved to meet the needs of its users, both on the MYOB Advanced and Acumatica platforms. With consistent new releases, Version 6 of Velixo is the most recent to be introduced to the market. It may have been some time since you deployed Velixo in your system, so there may be new functions you might not be aware of – or how they can make you even more efficient.
How to use List Functions
The List Functions are a set of functions designed to do more than just present numbers -they also fulfil an informative purpose. When combined with standard Microsoft Excel™, they produce powerful views for use within reports. The most common of these, which is accessed from the Excel ribbon as the “Insert Chart of Accounts” is effectively the function EXPANDACCOUNTRANGE returning the full chart within a Dynamic Array. Once inserted, the dynamic array can be used for many things, including in a Dropdown selection, as shown below.
Bring your accounts with history into your reports with the ACCOUNTSANDSUBACCOUNTSWITHHISTORY function
The ACCOUNTSANDSUBACCOUNTSWITHHISTORY Function is extremely useful when assessing data. It returns sorted groups (from lowest to highest) of accounts and subaccounts (first by account, then by subaccount) and branches as a dynamic array. It also has an alternate role in the building of dynamic reports by returning sorted groups of accounts for which there is account history. The use of ranges allows detailed reports, as opposed to summary reports say by Account Class, to be made dynamic. This will also update the list of accounts in real time, across ledger and branch changes in the report. The ACCOUNTSWITHHISTORY function works in a similar way, except without returning sub-accounts in the array.
Use TEXTSPLIT to make life easier
The TEXTSPLIT function takes a delimited list of values and separates the values into a dynamic array list running down the page. A good example is taking a set of values from an independent csv file (comma separated values) and turning them into a dynamic array list to use as part of a report.
How FINANCIALPERIODOFFSET can work to compare across periods?
How often do you want to compare periods and ensure that the comparisons change as you change the current period? The FINANCIALPERIODOFFSETOFFSET allows you to do just that in your report negating the need for complex programming to make things work with a macro because the periods are held as text and not numbers.
This will return the fifth financial period after the December 2018 period. In this case, it will return 01-2019.
The most important function in Velixo
The Velixo Support function can be accessed from within your Velixo Ribbon. It has access to all these functions and more. When you are struggling to make a report work, inbuilt support can save both time and money as well as providing a great source of Velixo knowledge.
Velixo’s functions are also compatible with Excel’s “Intellisense” feature. Intellisense shows you the arguments you need to complete a function, as well as the stage you are up to so that you do not get lost on the more complex function. Intellisense can also find the best match for the function that you need.
Harness the Power of Velixo Functions
Catch up on our MYOB Advanced Deep Dive webinar where we explored how to better harness the power of Velixo functions for report building. Watch the full webinar below or find more interesting content on our Youtube channel.