Functions for dates, finance and more

In the sections getting started with formulas and getting started with functions the basics of this topic are presented. This section is about more advanced functions.

We discussed the use of functions for text and numbers in the previous sections. There are, however, a lot more functions in the Berkeley Studio. We will show and explain some examples here. Please note that while we introduce a couple of important functions in this section, we do not discuss all functions. The finder in the Studio has an overview of all functions with a short explanation. Examples of models that show how to use some of these functions can be found at the here (dates) and here (finance).

Functions for dates

Probably the most used functions for dates are the functions now() and age(). They are discussed in the guided tour. However, you can do much more with dates in the Berkeley Studio.

Adding days to a date

The function adddays() is used to add a certain amount of days to a date. For example, image we need to now the date 90 days from a given date. You can’t just skip three months, you’ll need to carefully add exactly 90 days. Fortunately, there is the adddays() function. It takes any date as first parameter, any number of days as second parameter. Let’s say we want to know the date 90 days from now:

adddays(now(), 90)

As you can see, we used both the adddays() function and the now() function to calculate the right date! You can use a specific date as well:

addays('01-01-2018', 90) = 01-04-2018

There are also functions to add months or years to a date: addmonths() and addyears().

Days between two days

Sometimes, you’ll need to know how many days are between two dates. In that case, the daysbetween() function is exactly what you need. It takes two dates and returns the amount of days in between them! Take a look at the example:

daysbetween('01-01-2018','01-02-2018') = 31

Functions for finance

The Berkeley Studio also includes various functions for finance. Since none have as of yet been discussed, let’s start with a few examples!

In some decision trees it might be necessary to transform numbers into written text. For example, instead of 2010 you need ‘two thousand and ten’. In that case, the functions inwords(), inlegalwords() and inlegalfinancialwords() are what you are looking for. This first two functions transform a number in words, the second also transforms it into a currency:

inwords(512, 'en') = five hundred twelve
inlegalwords(512, 'en') = five hundred and twelve
inlegalfinancialwords(512.12, 'dollar', 'cent', 'en') = five hundred and twelve dollar and twelve cent

All three functions are available in English, Dutch, German and Portugese.

Percentage

The percentage() function makes calculating with percentages easier. It is a very simple yet useful function that divides a number by 100:

percentage(90) * 10 = 90
percentage(50) * 150 = 75

Other finance functions

The finance tab in the finder has several more complicated functions that calculate loans or rates. It is beyond the scope of this section to explain them. It is however, useful to know that they are there.

Other functions in the Berkeley Studio

The Berkeley Studio finder has four more tabs on functions: Advanced, Text production, Datasets and Interfaces. These functions are rarely needed by normal users of the Studio, and are therefore not discussed in this section. They might show up at the relevant sections, for example on data sets, in which case they are explained there.