You are here:

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 for performing calculations. When you select an expression category, the Selection window provides various operators and expressions that you can add to the formula text field above simply 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".

Aggregation

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
Average	Returns the average of the values.	`Average([column name])`
LetzerWert	Returns the last value from the specified column (not necessarily the largest value).	`LastValue([ColumnName])`
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])`
Max - Maximum	This function is only suitable for numbers, since it returns the largest occurring value.	`Max([column name])`
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])`
Mode	Calculates the modal value (most common value) of the specified spade.	`Modal value([column name])`
Sum	Calculates the column total of the specified column.	`Sum([column name])`

Condition

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 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. Note: Note: Same values always have the same rank. The next ranks will then be skipped accordingly.	`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.

Date

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	Delivers 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.

Workbook

Here you have access to the properties of the map folder, which you can enter in the properties (menu file → 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
Filepath	Returns the file name of the map folder.	`Workbook.file path`	{`workbook.filepath`} <font color="#ffff00">-=→=- proudly presents

Constants

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 content.	`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

Mathematical

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

		Syntax	Example
Abs - Absolute value	Gibt den Absolutbetrag einer Zahl zurück (die Zahl mit positivem Vorzeichen).	`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(«Zahl»)`	{`Cos(1.0)`} → 0.5403
Truncate	Converts a number with decimal places into a number without decimal places, always rounding down to the next smaller integer (for negative numbers, rounding up).	`Ganzzahl(«Zahl»)`	{`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 the base e.	`Log(«Zahl»)` `Log(«Zahl»,«basis»)`	{`Log(1000,3)`} → Log₁₀(1000) = 3 {`Log(1000)`} → Log_e(1000) = 6.907755
Log10 - Logarithm (base 10)	Returns the logarithm of a specified number to base 10. Example: Log₁₀(1000) = 3	`Log10(«Zahl»)`	{`Log10(1000)`} → Log₁₀(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)`} → 2⁸ = 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

Text box

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, where the length of the returned text must be specified.	`Links(«text»,«zeichen»)`	{`Links("53177", 2)`} → "53"
Right	Returns the end of the specified text, where the length of the returned text must be specified.	`Rechts(«text»,«zeichen»)`	{`Right("Bonn, Köln", 4)`} → "Cologne"
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

Conversion

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 into a 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

Comparison

The following operators are available for the 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". 23 → 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 → 3² = 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 creating your own classifications or groupings.	`IIF(«bedingung»,«wahr»,«falsch»)`	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")` You can also create more complex groupings by nesting several IIF statements. 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 of EasyMap Xplorer

In order to display and use calculated columns in EasyMap Xplorer, the columns must not only be specially formatted, but they must also be calculated first. It is important that texts or data can be taken from individual columns and output summarized in a new column.

Example

In the example data, the Name column is used to display the name as a link to the company's homepage.

Customer number	name	homepage	turnover	Calculated column - Display Internet
125	customer A	http://www.kundeA.de	1.234.500	<a href="http://www.kundeA.de/"target="_blank">Kunde A</a>
351	customer B	http://www.kundeB.de	35.125	<a href="http://www.kundeB.de/"target="_blank">Kunde B</a>
562	customer C		254.896
149	customer D	http://www.kundeD.de	238.548	<a href="http://www.kundeD.de/"target="_blank">Kunde D</a>