In our last post in January, we shared an update on our new formula engine, which will support arrays, ranges and lots of new functions. We closed by writing that we hoped that our new release would “see the light of day in March.”
The bad news is that our next release will take a little longer to reach you. The good news is that we’re pushing back the release for a good reason: we want to get two additional features done, which we think will make your day-to-day work with Calcapp easier.
Those features are support for decimal commas in formulas and much-improved formula documentation.
Decimal commas in formulas
Since its inception, Calcapp has only recognized decimal points (“.”) as decimal separators and commas (“,”) as parameter separators in formulas. This is in line with how numbers are written in countries like the US, Britain, Australia, New Zeeland, China, Japan, India and parts of Africa.
However, most parts of South America and Europe (including Russia), as well as parts of Africa, use decimal commas, making Calcapp formulas feel foreign in these countries. We often stress that you can frequently carry over formulas from spreadsheets to Calcapp simply by copying and pasting them, but this has sadly not worked well for our European and South American customers.
Apps produced with Calcapp have supported languages other than US English for more than three years now. Numbers are formatted according to the language of the app and functions like PARSENUMBER and FORMATNUMBER correctly handle the decimal separator of the configured language. In other words, apps have handled decimal commas correctly for years now, but not the app designer.
Programming languages typically use decimal points exclusively. Spreadsheets, however, with very few exceptions either allow the decimal separator to be set manually or follow the conventions of the host operating system. Spreadsheets that use decimal commas as decimal separators typically use semicolons (“;”) as parameter separators instead of commas.
Ironically, we’re a Swedish business, and decimal commas are exclusively used as decimal separators here. We’re not only Swedish, though, we’re also software developers. As programming languages exclusively support decimal points, it appears that we have grown a little too comfortable with decimal points to realize that this has been a major pain point our customers have experienced.
Enough is enough. We’re currently working on supporting decimal
commas natively in formulas. There will be a preference you’ll be
able to toggle, which will then make all formulas you edit use
decimal commas as decimal separators instead of decimal points. This
means that you’ll soon be able to write IF(Field1 >= 3.14,
1.23) as IF(Field1 >= 3,14;
1.23).
The Calcapp 4 formula engine actually introduces another separator which is affected by this change, in addition to decimal and parameter separators: statement separators. As mentioned elsewhere, we’ll soon support action formulas, which you’ll be able to associate with buttons. When a button is activated, the formula is run, and these formulas will be able to execute multiple actions (or “statements”), like sending multiple reports and conditionally resetting fields.
To separate actions, or statements, you’ll need to use a statement separator. If Calcapp Creator is configured to use a decimal point, that’s a semicolon:
IF(Field1 >=
3.14, RESET(Field1)); ALERT("Done!")
This example resets Field1 only if its value is greater than or equal to 3.14. It then (unconditionally) displays the message “Done!” in a popup box. The two statements are separated by a semicolon.
If Calcapp is configured to use a decimal point, you instead need to write two semicolons:
IF(Field1 >=
3,14; RESET(Field1));; ALERT("Done!")
Much-improved function documentation
Back in 2016, we overhauled our formula engine, tweaking the way you write formulas slightly—to improve compatibility with spreadsheets—and added support for hundreds of spreadsheet functions. Overnight, we went from supporting around 20 functions to supporting 244. (Today, that number is 281.)
We knew that we needed documentation—for the functions themselves as well as their parameters—but writing high-quality documentation for hundreds of functions is not an easy task, and hard to justify for a product that was then in beta and hadn’t quite found its market yet. As a result, the documentation we did produce was quite terse.
Times have changed. The new functions we are adding can be used in many ways, and there may be alternatives to them that are preferable in some contexts. As a result, they need examples and room for a comprehensive discussion section.
We have re-written or expanded the documentation for most functions. There are exceptions, though. We have done little to change the documentation for most of the specialized statistical and financial functions we offer (like GAMMA.DIST, which still only “calculates values for a Gamma distribution,” with no additional content). All functions, except for compatibility functions, have at least one example, though.
The new documentation is far too voluminous to fit in the old reference sidebar. The plan is to host the documentation on the main website, and only include the first sentence and the parameter documentation in the reference sidebar. The full documentation will then be available from the sidebar through a link.
An added benefit of having the documentation available on the main website will be that you’ll more easily be able to make hard copies of it. Search engines will also have an easier time finding it.
To illustrate our new approach to documentation, the next section is a draft copy of our documentation for the new FILTER function.
A draft version of the new FILTER function documentation
Filters the first array using the second parameter. For instance,
FILTER({ 1, 2, 3 }, { FALSE, TRUE, TRUE }) returns
{ 2, 3 }. 1 is not part of the returned array, as the
corresponding element in the second array is FALSE. 2 and 3 are both
part of the returned array, as the corresponding elements in the
second array are both TRUE.
In other words, the returned array contains an element found in the first array only if the corresponding element in the second array is TRUE. The two arrays must be equal in size.
The second array is often not provided explicitly. Rather, an
operation is typically used that returns a logical array. For
instance, FILTER(Field1:Field3, Field1:Field3 > 5)
returns fields whose values are greater than 5.
(The Field1:Field3 range is a short-hand way of
expressing an array containing Field1 and Field3,
as well as any fields that appear between them, such as
Field2. If only Field2 appears between the other
two fields, Field1:Field3 and { Field1, Field2,
Field3 } are equivalent.)
The second array does not need to reference the same array as the
first array. FILTER({ "Sally", "Ed", "Luke", "Jenny" }, { 90,
62, 91, 82 } > 85), where the first array contains student
names and the second array contains their test scores, returns an
array of names of the students who scored more than 85.
Use the SIZE function to determine the number of elements returned by
FILTER. SIZE(FILTER(Field1:Field100, Item > 5))
returns the number of fields, in the Field1:Field100
range, whose values are greater than 5.
Combining multiple conditions
Use the logical operators || to express “logical or”
(disjunction) and && to express “logical and”
(conjunction). FILTER(Field1:Field3, (Field1:Field3 > 5) ||
(Field1:Field3 < 2)) returns those fields whose values are
greater than 5 or less than 2. FILTER(Field1:Field3,
(Field1:Field3 > 5) && ISODD(Field1:Field3)) returns
only those fields whose values are both greater than 5 and are odd
numbers.
Traditionally, spreadsheets use the + operator to
express “or” and the * operator to express “and.”
Calcapp supports these operators too. (The functions OR and AND
cannot be used in this context, as they return a single logical
value, not a logical array.)
Using a formula fragment to filter elements
Instead of passing a logical array as the second parameter, you can
also use a formula fragment which is expected to return TRUE if the
element should be included and FALSE otherwise. FILTER({ 1, 2,
3 }, { 1, 2, 3 } > 1) can also be expressed as
FILTER({ 1, 2, 3 }, Item > 1).
The formula fragment you supply as the second parameter is run once
per element. The element can be accessed as the Item
value. Moreover, the index of the element (1 for the first element, 2
for the second element, etc) can be accessed as the
Index value and the source array—given as the first
parameter to FILTER—as the Source value. Including only
the last three elements can be achieved using a formula like this
one: FILTER(SEQUENCE(100), Index > SIZE(Source) - 3).
This variant is useful if you need to compare array elements only against themselves. It has the benefit of saving you from having to repeat the source array in the formula. This feature is specific to Calcapp and is not found in spreadsheets.
(Using this variant when you need to examine another array is
possible, but is not as convenient. FILTER({ "Sally", "Ed",
"Luke", "Jenny" }, { 90, 62, 91, 82 } > 85) would be
expressed as FILTER({ "Sally", "Ed", "Luke", "Jenny" }, INDEX({
90, 62, 91, 82 }, Index) > 85), using the
Index value together with the INDEX function to extract
the correct value from the other array.)
Using FILTER instead of the *IF functions
When combined with other functions, FILTER can be used as a replacement for functions like AVERAGEIF, COUNTIF and SUMIF. These functions all boil down to filtering an array before applying an additional operation.
SUMIF, for instance, first filters an array based on a condition and
then adds the resulting numbers together, returning the result.
SUMIF({ 1, 10, 100, 1000 }, ">25") returns 1100,
because only 100 and 1000 are greater than 25. SUM(FILTER({ 1,
10, 100, 1000 }, Item > 25)) and SUM(FILTER({ 1, 10,
100, 1000 }, { 1, 10, 100, 1000 } > 25)) return the same
result.
Similarly, COUNTIF({ 1, 10, 100, 1000 }, ">25")
returns 2, because exactly two array elements are greater than 25.
SIZE(FILTER({ 1, 10, 100, 1000 }, Item > 25)) is
equivalent—the FILTER invocation returns { 100, 1000 }
and SIZE returns the size of that array, 2.
AVERAGEIF({ 1, 10, 100, 1000 }, ">25") returns 550,
because that is the average value between 100 and 1000.
AVERAGE(FILTER({ 1, 10, 100, 1000 }, Item > 25)) is
equivalent.
When given a third parameter, AVERAGEIF, COUNTIF and SUMIF apply the
condition to the first array, while applying the operation
(averaging, counting and summing) to the first array. SUMIF({
"Dave", "Sally", "Sally" }, "Sally", { 30, 40, 50 }) adds
together 40 and 50, but not 30, as these numbers are associated with
the text string “Sally”, which the condition stipulates. The
equivalent formula using FILTER is SUM(FILTER({ 30, 40, 50 }, {
"Dave", "Sally", "Sally" } = "Sally")).
Using FILTER instead of the *IFS functions
AVERAGEIF, COUNTIF and SUMIF also come in versions designed to be used with multiple conditions: AVERAGEIFS, COUNTIFS and SUMIFS. There are also two *IFS functions with no *IF counterpart: MINIFS and MAXIFS, which return the smallest and largest number, respectively, from an array, provided that a number of conditions are met.
AVERAGEIFS({ 90, 62, 91, 82 }, { "F", "M", "M", "F" }, "F", {
"CA", "CA", "AZ", "AK" }, "AK") returns an average of the test
scores obtained by female students who reside in Arkansas
(abbreviated “AK”). Here, only one test score matches (82) and is
returned, because while two female students appear in the data, only
one of the students resides in Arkansas.
The equivalent formula using FILTER is AVERAGE(FILTER({ 90, 62,
91, 82 }, ({ "F", "M", "M", "F" } = "F") && ({ "CA", "CA", "AZ",
"AK" } = "AK"))).
The FILTER version is far more versatile. AVERAGEIFS can only be used
with “logical and” and not “logical or” (or any other combination of
logical operations). What if we want the average of all test scores
associated either with female students or students residing in
Arkansas? AVERAGEIFS does not provide this feature, but using FILTER,
it’s as easy as turning && into ||:
AVERAGE(FILTER({ 90, 62, 91, 82 }, ({ "F", "M", "M", "F" } =
"F") || ({ "CA", "CA", "AZ", "AK" } = "AK"))).
FILTER, MAP and REDUCE
FILTER, MAP and REDUCE are commonly used together, as a data processing pipeline to transform data. FILTER is used to remove irrelevant elements from an array, MAP is used to transform the remaining elements and REDUCE, finally, is used to transform the resulting array to a single value.
Instead of REDUCE, a simpler function can be used which reduces an array of values to a single value. The most popular such functions are SUM (which adds all array elements together and returns the result) and AVERAGE (which returns an average of all the array elements).
A single formula can use multiple invocations of MAP and FILTER. For instance, an innermost FILTER invocation can filter the raw array once, and then hand this data to MAP, which transforms the filtered data. This data can then, once more, be given to FILTER, which filters out additional elements, and so.
The formula SUM(FILTER(MAP(FILTER({ "$326.60", "€402.80",
"$290.00", "$128", "3002 SEK" }, STARTSWITH(Item, "$")),
PARSENUMBER(Item)), Item > 200)) starts off with the text
array { "$326.60", "€402.80", "$290.00", "$128", "3002 SEK"
}, listing amounts in various currencies. Working our way
outwards from the array, FILTER is then applied to the array, and
uses the formula fragment STARTSWITH(Item, "$") to only
include array elements which start with a dollar sign. That leaves
{ "$326.60", "$290.00", "$128" }. MAP is applied to this
array, with the formula fragment PARSENUMBER(Item),
which converts the text array with textual amounts to a number array
holding the same amounts: { 326.6, 290, 128 }. FILTER is
then applied to this array using this formula fragment, Item
> 200, which filters out all elements which are not greater
than 200. That leaves the array { 326.6, 290 }. Finally,
SUM is applied to this array, returning the grand total 616.60.
Parameters:
values the values to filter.
inclusions either an array or a formula fragment. If
this parameter is an array, it should be a logical array with the
same size as the array of values to filter. The returned array only
contains values where the corresponding element in this array is
TRUE. If this parameter is a formula fragment, it is run once per
array element and is expected to return TRUE only if the
corresponding array element should be included in the returned array.
To do its work, it has access to the three named values
Item, the array element under consideration,
Index, the index of the array element (1 for the first
element, 2 for the second element, etc) and finally
Source, which is a reference to the array to filter,
given as the first parameter to FILTER.
fallbackValues the values to return if no values matched
the filter. If there are no fallback values, an empty array is
returned.
Result: a filtered version of the given values. If
no values match the filter, an empty array is returned, unless the
fallbackValues parameter is provided, in which case
those values are returned instead.
Examples:
FILTER({ 1, 2, 3 }, { FALSE, TRUE, TRUE })
returns { 2, 3 }. 1 is not part of the returned array,
as the corresponding element in the second array is FALSE. 2 and 3
are both part of the returned array, as the corresponding elements in
the second array are both TRUE.
FILTER({ 1, 2, 3 }, { 1, 2, 3 } > 1)
returns an array containing the elements which are greater than one,
{ 2, 3 }. { 1, 2, 3 } > 1 returns the
logical array { FALSE, TRUE, TRUE }.
FILTER({ 1, 2, 3 }, Item > 1)
returns an array containing the elements which are greater than one,
{ 2, 3 }. This variant uses a formula fragment, which is
run once per array element to determine whether the element should be
part of the result. The formula fragment is expected to return TRUE
if that is the case.
SIZE(FILTER({ 1, 2, 3 }, Item > 1))
returns the number of array elements which are greater than one. As only 2 and 3 are greater than 1, 2 is returned. This variant uses a formula fragment, which is run once per array element to determine whether the element should be part of the result. The formula fragment is expected to return TRUE if that is the case.
FILTER({ 1, 2, 3 }, V -> V > 1)
returns an array containing the elements which are greater than one,
{ 2, 3 }. This variant uses a formula fragment, which is
run once per array element to determine whether the element should be
part of the result. The formula fragment is expected to return TRUE
if that is the case. This fragment renames the Item
value, which references the array element under consideration, to V.
For longer formula fragments, renaming a value can make the resulting
formula fragment much smaller. Alternatively, renaming a value can be
used to give it a name that more accurately describes the value.
FILTER({ 10, 20, 30 }, Index >= 3)
returns { 3 }, as only the last array element has an
index greater than or equal to 3. (The first element has an index of
1, the second an index of 2, etc.)
FILTER({ 10, 20, 30 }, (V, I) -> I >= 3)
returns { 3 }, as only the last array element has an
index greater than or equal to 3. (The first element has an index of
1, the second an index of 2, etc.) Here, both the Item
and Index values are renamed to V and
I, respectively. Even if the Item value is
not needed, it still needs to be renamed, as it appears before the
Index value.
FILTER({ 10, 20, 35 }, MOD(Item, 10) = 0)
returns array elements which are evenly divisible by 10, meaning that
{ 10, 20 } is returned.
FILTER({ 10, 20, 35 }, MOD(Item, Field1) = 0)
returns all array elements which are evenly divisible by
Field1.Value</em>, provided by the user.
FILTER({ 10, 20, 35 }, MOD(Item, 50) = 0)
returns array elements which are evenly divisible by 50. As there are no such array elements, an empty array is returned.
FILTER({ 10, 20, 35 }, MOD(Item, 50) = 0, { -50 })
returns array elements which are evenly divisible by 50. As there are
no such array elements, the third parameter is returned, { -50
}.
FILTER({ "Sally", "Luke", "Jenny" }, { 90, 91, 82 } > 85)
returns { "Sally", "Luke" }, which are the names of the
students who scored higher than 85, provided that the first array
contains student names and the second array contains their scores.
FILTER({ Field1, Field2 }.Color, { Field1, Field2 } > 3)
returns the colors of the fields whose values are greater than 3 as an array.
FILTER(Field1:Field100.Color, Field1:Field100 > 3)
returns the colors of the fields whose values are greater than 3 as
an array. Field1:Field100 is short-hand for an array
which includes Field1, Field100 and all other
fields which appear between them.
FILTER(Field1:Field100.Value, MOD(Index, 5) = 0)
returns the values of every fifth field, among those listed.
Field1:Field100 is short-hand for an array which
includes Field1, Field100 and all other fields
which appear between them.
SORT(UNIQUE(FILTER({ 32, 8, 8, 16, 4, 2, 1 }, Item > 7)))
returns { 8, 16, 32 }, which includes only those
elements of the { 32, 8, 8, 16, 4, 2, 1 } array which
are greater than 7. UNIQUE ensures that there are no duplicate
values, and SORT ensures that the returned array is sorted.
Current status
We’re currently implementing support for commas as decimal separators in formulas. The new formula documentation has already been written. It has not yet been proof-read, though, nor have we written the software which will generate HTML documentation for the main website. (Should there be a search field? Should one page be generated for every function, or one page per category, which would group all text functions together? There are a few questions to resolve here.)
There are also many new button types (which will be able to do things like execute action formulas and open the system map app). They have not yet been implemented, but are not expected to be time-consuming.
We also need to ensure that the migration process is smooth, and that all paid apps will continue to run with no interruptions once the new formula engine is live.
This will be one of the biggest Calcapp upgrades we have ever released, and without a doubt the biggest update our formula engine has ever received. We’re anxious to get it into your hands and we look forward to seeing the amazing apps you’ll create with it.