Calculated Column, Formulas, and Filter Expressions

You can use a formula editor to specify a calculation that is based on other contents of the table. You can, for example, calculate percentages or total values from different columns, or define an expression for a filter.

Expression Categories

As an aid, easymap offers various expressions within the formula editor to perform calculations. If you select an expression category, different operators and expressions are available in the window Selection. You can add them to the formula text field above by double-clicking.

Note: Always use a period instead of a comma as decimal separator (e.g. 0.25). Please do not use thousands separators; strings in quotation marks "ABC".

 

   Syntax Example
AddressProperty Provides additional information about an address. AddressProperty([address], "identifier") AdressProperty([address column], "identifier")
Quality of results Returns the quality of results for an address. Quality([column name]) Quality([column name], cStreet|cHouse number|cPostcode|cLocation)
house number Returns the house number to an address. House number([address]) House number([address column])
Country Delivers the country to an address. Country([address]) Country([address column])
Location Delivers the location to an address. MOrt([address]) Location([address column])
District Returns the city district to an address. District([address]) District([address column])
Postcode Delivers the postal code to an address. Postal code([address]) Postcode([address column])
street Delivers the street to an address. Street([address]) Street([address column])

The expression category Aggregation is only available in the context of tables (calculated columns, filters). Calculates an aggregate value from all values in a column according to a predefined aggregation rule.

    Syntax Example
Count Calculates the number of values in the specified column. Number([column]) Quantity([customer no.])
Average Returns the average of the values. Average([column name]) Average([turnover])
First value Returns the first value from the specified column (not necessarily the largest value). FirstValue([column name]) FirstValue([CustomerNo])
Last value Returns the last value from the specified column (not necessarily the largest value). LastValue([column name]) FirstValue([CustomerNo])
List Returns all values from the specified column in the form of a list (the result is limited to 1024 characters). The values are displayed comma separated (without spaces). List([column name]) List([CustomerNo])
Max - Maximum This function is only suitable for numbers, since it returns the largest occurring value. Max([column name]) Max([turnover])
Median Calculates the median (mean value of the sorted data) of the specified column. Median([column name]) {Median([Quantity])}
Min - Minimum This function is only suitable for numbers, since it returns the smallest occurring value. Min([column name]) Min([turnover])
Mode Calculates the modal value (most common value) of the specified spade. Modal value([column name]) Modal value([number of pieces])
Sum Calculates the column total of the specified column. Sum([column name]) Sum([turnover])

Here you will find some functions for conditional formatting for desired columns. The result of the column is displayed as True/False result, so that a filter for True or False is possible in the table for result display.

    Syntax Example
Top 10 Elements Returns True if the value falls in the upper x elements.   Obere([column], 10) = True
Top 10 Percent Returns True if the value falls in the upper x percent of the elements. ObereProzent(«Spalte», «Anzahl») {Upperpercent([Turnover], 10)} → True
Above average Returns True if the value is above the average. ÜberDurchschnitt(«Spalte») {AboutAverage([Turnover])} → True
Below Average Returns True if the value is below the average. UnterDurchschnitt(«Spalte») {BelowAverage([turnover])} → True
Bottom 10 Elements Returns True if the value falls in the lower x elements. Untere(«Spalte», «Anzahl») {lower([Turnover], 10)} → True
Bottom 10 Percent Returns True if the value falls in the lower x percent of the elements. UntereProzent(«Spalte», «Anzahl») {LowerPercent([Turnover], 10)} → True
Rank Returns the rank that the value occupies within all values of the column or a group within the column.
If only one argument is specified, the rank is returned within all values of the column, starting with the highest value.
With a 2nd argument, the value is returned within all values of the column. (optional) argument, you can specify whether the ranking starts with the highest value (argument=true) or the lowest value (argument=false).
With a 3rd (optional) argument, the ranking can be related to a group within the table (e.g. within a territory). The argument specifies a different column to be used for grouping.

 Rang(«RangSpalte»,
«absteigenderRang» «gruppierungsspalte»)

{Rank([Turnover]} → 1 for the largest value of the column, 2 for the next largest, and so on.

 

{Rank([Turnover],true,[Territory]} → 1 for the largest value within those values that have the same value in the Territory column as this row.

Various functions are available to output the current date or format date values.

    Syntax Example
DateAdd Adds the specified number of years, months, days, hours, minutes, or seconds to the specified date. DateAdd(«wert», «datum», «cJahr|cMonat|cTag|cStunde|cMinute|cSekunde») {DateAdd(5, „01.01.2016“, cday)} → „06.01.2016“
DateDiff Returns the difference between start and end date in days, hours, minutes or seconds. DateAdd(«wert», «datum», «cJahr|cMonat|cTag|cStunde|cMinute|cSekunde») {DateAdd(5, „01.01.2016“, cday)} → „06.01.2016“
Date Returns the current date (without time) in the format of the current system settings. Date {date} → „10.01.2015“
DateMonth Returns year and month as text DateMonth {DateMonth} → „2015-01“
DateQuarter Returns year and quarter as text DateQuarter {DateQuarter} → „2015-Q1“
DateDay Returns year, month and day as text DateDay {DateDay} → „2015-01-17“
Date/time from text Converts the specified text to a date/time value using the current country setting. DateTimeFromString {DateTimeOffText} → „10.01.2015 13:05“
Date/time for text Converts a date/time value into a text. The format parameter determines how the date is output. DateTimeToString {DateTimeToText} → "January 10, 2015"
DateWeek Returns year and week as text DateWeek {DateWeek} → „2015-35“
Year Returns the year of the specified date or the current date if the argument is not specified.

year()

Jahr(«datum»)

{Year("10.01.1992")} → „1992“

{Year()} → „2015“
Now Returns the current date (incl. time). The formatting of this date depends on the system setting. Now {Now} → „10.01.2015 15:03“
minute Returns the minute of the specified date or the current date if the argument is not specified. minute {Minute("10.01.2015 15:03")} → 3
Month Returns the month of the specified date or the current date if the argument is not specified.

month()

Monat(«datum»)

{Month(03/10/2006)} → 3

{Month()} → 10
Second Returns the second of the specified date or the current date if the argument is not specified. Sekunde() Sekunde(«datum»)

{second()} → 23

{second("10.01.2015 13:03:07")} → 7
Hour Returns the hour of the specified date or the current date if the argument is not specified. Stunde() Stunde(«datum»)

{hour()} → 11

{Hour("10.01.2015 15:03")} → 15
Day Returns the day number of the specified date or the current date if the argument is not specified. Tag() Tag(«datum»)

{Tag("10.01.2015")} → 10

{Tag()} → 24
Time Returns the current time in the format of the current system settings. Time {Time} → „15:03“

Another example for a Filter:

[Column name]>=DateTimeFromText("15.03.2018") and [Column name]<=DateTimeFromText("10.04.2018")

This filter enables you to display and evaluate your data reduced to a specific time period.

Here you have access to the properties of the workbook, which you can enter in the properties (File menu → Properties).

    Syntax Example
Caption Returns the name of the sheet in which the object is located. workbook.name {workbook.name} <font color="#ffff00">-=→=- proudly presents
Filename Returns the name of the workbook. Workbook.filename {workbook.filename} <font color="#ffff00">-=→=- proudly presents
File name without extension The file name of the map folder (without file extension) workbook.filenameShort File nameShort = "unnamed"
Filepath Returns the file name of the map folder. Workbook.file path {workbook.filepath} <font color="#ffff00">-=→=- proudly presents

Constants always have the same value regardless of context and can be used to compare expressions with it.

    Syntax Example
False Returns the truth value FALSE. False

Use a filter expression to specify that the table should only contain those rows where the Value column is not empty.

ActualLeer([Value])=Wrong
Zero

Returns an empty value.

Note: The constants zero and 0 have different meanings. The number 0 can only be used for comparisons with arithmetic expressions. The constant zero can be used to check whether a variable has no contents.

 Zero  
True Returns the truth value TRUE. True

Use a filter expression to specify that the table should only contain those rows where the Value column is empty.

ActLeer([Value])=True

Here you have access to the properties of the sheet in which the object is located.

    Syntax Example
Abs - Absolute value Returns the absolute value of a number (the number with a positive sign). Abs(«Zahl»)

{ Abs(-5.2))} → |-5.2| = 5.2

{ Abs(5.2))} → |5.2| = 5.2

Cos - Cosine Returns the cosine of the angle given in radians. Cos("Number") {Cos(1.0)} → 0.5403
Integer Converts a number with decimal places into a number without decimal places, always rounded down to the next smaller integer (rounded up in case of negative numbers). Integer ("Number")

{Integer(5.2)} → 5

{Integer(5.9)} → 5

{Integer(-5.9)} → -5
Log - Logarithm Returns the logarithm of a number to the specified base. If the base argument is not specified, the natural logarithm is returned to base e.

Log(«Zahl»)

Log(«Zahl»,«basis»)

{Log(1000,3)} → Log10(1000) = 3

{Log(1000)} → Loge(1000) = 6.907755
Log10 - Logarithm (base 10) Returns the logarithm of a specified number to base 10. Example: Log10(1000) = 3 Log10(«Zahl») {Log10(1000)} → Log10(1000) = 3
Max - Maximum Returns the larger of two numbers. Max(«Zahl»,«Zahl») {Max(5.2, 10.4)} → 10.4
Min - Minimum Returns the smaller of two numbers. Min(«Zahl»,«Zahl») {Min(5.2, 10.4))} → 5.2
Potency Power a specified number with the specified exponent ("a to the power of b", ab). Potenz(«Zahl».«Zahl») {Potency(2,8)} → 28 = 256
Round Rounds the specified number to the specified number of decimal places. Rudnen(«Zahl».«stellen») {Rounds(5.27, 1)} → 5.3
Sin - Sine Returns the sine of the angle specified in radians. Sin(«Number») {Sin(1.0)} → 0.8414
Tan - Tangent Returns the tangent of the angle specified in radians. Tan(«Zahl») {Tan(1.0)} → 1.5574
Sqrt - Square root Returns the square root (√) of a specified number. Example: Root(4.0) = 2 Wurzel(«Zahl») {root(4.0)} → √4.0 = 2

This function allows you to drag the coordinates from an address.

Note: This function is, for data protection reasons, not possible for Worldwide Geocoding.

    Syntax Example
latitude Returns the latitude of a coordinate. Lat([Coordinate]) Lat([coordinate column]) = 53.1
Coordinate Returns a coordinate to the specified text. LatLng([coordinate]) LatLng("51.234;8.234")
degree of longitude Returns the longitude of a coordinate. Lng([coordinate]) Lng([coordinate column]) = 8.3

In this category some functions are offered to work with texts or character strings.

    Syntax Example
„“ - Empty character string Can be used to compare with an empty string. ""

Use a filter expression to specify that the table should only contain those rows where the Value column contains an empty character string.

[Value]="""
Contains Checks whether a semicolon-separated list contains an entry. Enthält(«text»,«muster»)

{Includes("10;11;12", "10")} → True

{Includes("A;B;C", "D")} → Wrong
Replace Replaces all occurrences of a particular string in the text with another. Ersetzen(«text»,«suchen»,«ersetzen») {Replace("Bonn, Köln, Dresden", "Köln", "Hamburg")} → "Bonn, Hamburg, Dresden"
find Returns the position of the first occurrence of a string within another. left([column name],Find(",", [column name],0)))

Can be used in combination with the Links or Part functions to split a string at a separator and return only the left or right part of that character.

examples

{Links("Meier, Joachim",Find("," "Meier, Joachim",0)) -> Meier}

{Part("Meier, Joachim",Find("," "Meier, Joachim",0)+2, 100) -> Joachim}

-> +2 so that the character string begins after the comma and (here existing) space.

Smoothing Removes spaces from the beginning and end of the specified text. Glätten(«text») {Smooth("Bonn ")} → "Bonn"
UCase - Uppercase Converts the specified text to uppercase. Groß(«text») {Large("Bonn, Cologne, Hamburg")} → "BONN, COLOGNE, HAMBURG"
IsEmpty Returns True if the specified value is zero or an empty text. IstLeer(«wert»)

{ActLeer("Text")} → Wrong

{ActLeer(""")} → True
LCase Converts the specified text to lowercase. Klein(«text») {Klein("Bonn, Cologne, Hamburg")} → "Bonn, Cologne, Hamburg"
Length Returns the number of characters of a text. Länge(«text») {Length("Text")} → 4
Left Returns the beginning of the specified text. The length of the returned text must be specified. Links(«text»,«zeichen») {Links("53177", 2)} → "53"
LinksFill up Fills the text of links with the specified number of spaces. This function can be used to align texts. Fill Links([Text], [Number]) LeftFill("Text", 10) = " Text"
Right Returns the end of specified text, where the length of the returned text must be specified. Rechts(«text»,«zeichen») {Right("Bonn, Köln", 4)} → "Cologne"
Filling up the law Fills the text from the right with the specified number of spaces. This function can be used to align texts. RechstAuffüllen([Text], [Number]) Right padding ("Text", 10) = "Text "
Mid Returns a specific part of the specified text. The start position and the length of the partial text are specified. Characters beyond the end of the text are ignored. Teil(«text»,«start»,«zeichen») {Part("Bonn, Cologne, Dresden", 6, 4)} → "Cologne"
Like

Returns True if the specified text matches the specified pattern.

The following special characters can be used to define the pattern.

? Stands for a single character

* Stands for 0 or more characters.

# Stands for one digit (0-9)

You can also use square brackets to define groups of characters, such as [ABC], [A-Z], [A-Za-z], [a-ex-z]. By inserting an exclamation mark, character groups can be negated, e.g. [! ABC].

 Wie(«text»,«muster») {How("Text", "T*")} → True
NewLine Constant for a line break. The constant can be used to output text in multiple lines. NewLine(«text»,«start»,«zeichen»)

{("Heading 1" + line break + "Heading 2")} → Heading 1

Header 2

Certain functions and operators require either numbers or texts to be linked.

In particular, texts containing numerical representations cannot simply be used in places where numbers are expected. This leads to type conversion errors.

You can use the conversion functions to force a different data type, although this is not always possible and can lead to runtime errors.

    Syntax Example
CFormat - value as formatted text Converts the specified value to a formatted text using the current country setting. CFormat(«wert»,«format») {CFormat(5.25, "0.#")} → "5,3"
CText - conversion to text Converts the specified value into a text using the current country setting. CText(«wert»)

{CText(5.2)} → "5,2"

{Length(CText(9999)} → 4
CWert - Conversion to Number Converts the specified value/text into a number using the current country setting. If the value cannot be converted into a number, a runtime error occurs. CWert(«wert») {CValue("5,2")} → 5.2
Decimal degree - conversion "G°M`S" to number Converts a degree value formatted in degrees/minutes/seconds to a decimal number. Dezimalgrad(«wert») {Decimal degree("51°14'4.2''"")} → 51,2345
Format - Value as formatted text (international) Converts the specified value to a formatted text using the international country setting. Format(«wert»,«format») {Format(5.25, "0.#")} → "5.3"
Text - conversion to text (international) Converts the specified value to text using the international country setting. Text(«wert») {Text(5.2)} → "5.2"
Val (international) Converts the specified value/text into a number using the international country setting. If the value cannot be converted into a number, a runtime error occurs. Wert(«wert») {Value("5.2")} → 5.2

The following operators are available for basic arithmetic operations and for comparing values.

    Syntax Example
Subtraction (-) Calculates the difference ("minus") between two values. «wert1»-«wert2»

[ColumnA]-[ColumnB] → "Value in new column".

8-3 → 5
Multiplication (*) Calculates the product ("multiply") of two values. «wert1»*«wert2»

[ColumnA]*[ColumnB] → "Value in new column".

2*3 → 6
Division (/) Calculates the quotient ("divided by") of two values. «wert1»/«wert2»

[ColumnA]/[ColumnB] → "Value in new column".

6/2 → 3
Potentiation (^) Calculates the power ("a to the power of b", ab) of two values (base and exponent). «basis»^«exponent» 3^2 → 32 = 9
Addition / Chaining (+)

Besides the mathematical operation, this operator also allows to concatenate strings.

For numbers: Calculates the sum of two values.

For string: Forms a common string by concatenating the two strings.

«wert1»+«wert2» {3+4} → 7 {"Ham "+"castle"} → "Hamburg"
Less than (<) Compares two values and returns True or False as the result. «wert1» < «wert2»

Filter expression to return all rows for which the value in columnA is smaller than the value in columnB.

{[ColumnA]<[ColumnB]}
Less equal (<=) Compares two values and returns True or False as the result. «wert1» <= «wert2»  
Not equal Operator (<>) Compares two values and returns True or False as the result. «wert1» <> «wert2»  
Equal (=) Compares two values and returns True or False as the result. «wert1»=«wert2»  
Bigger (>) Compares two values and returns True or False as the result. «wert1» > «wert2»  
Greater than or equal to (>=) Compares two values and returns True or False as the result. «wert1» >= «wert2»  
Equals Returns true if both values are equal according to the specified comparison type. Example: Equal("01067", "1067",similar) = yes Gleich(«wert»,«wert»,«cExact|cÄhnlich») {Same("01067", "1067",cSimilar)} = yes
IIF - If Returns one of two other expressions depending on the evaluation of an expression. The IIF statement is well suited for forming your own classifications or groupings. IIF("condition", "true", "false")

A new calculated column is to output the category "A customer" or "B customer" based on the sales. A customer with sales greater than 100000 is to be considered an A customer.

IIF([Turnover]>=100000, "A-Customer", "B-Customer")

By nesting several IIF statements, more complex groupings can also be formed.

If a customer with a turnover of less than 20000 is to be classified as a C customer, the following expression can be used for grouping in 3 categories:

IIF([Turnover]>=100000, "A-Customer", IIF([Turnover]>=20000, "B-Customer", "C-Customer"))

The statement can also be used to treat empty cells as if the value 0 were in them:

IIF([turnover]==zero,0.0,[turnover])

IsNull Returns True if the specified value is "value" zero. Especially for tables it can be used to check whether a field contains content or not. IstNull(«wert»)

{ActualNull("Text")} → Wrong

Define a calculated column that always outputs "J" if there is a value in columnA, and otherwise "N".

{IIF(ActualNull([columnA), "J", "N")}

Special features in the easymap explorer

For the display and use of calculated columns in easymap explorer the columns not only have to be specially formatted, but some of them have to be calculated first. Here it is important that texts or data can be taken from individual columns and output in a new column.

Example

In the example data, the entry from the column Weblink is used as text for the link to the company's homepage.

Web link homepage Calculated column - Display Internet
website http://www.lutumtappert.de &lt;a href="http://www.lutumtappert.de" target="_blank"&gt;Website&lt;/a&gt;