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»,
|
{
{ |
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. |
|
{ { |
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. |
|
{
{ |
Second | Returns the second of the specified date or the current date if the argument is not specified. | Sekunde()
Sekunde(«datum»)
|
{
{ |
Hour | Returns the hour of the specified date or the current date if the argument is not specified. | Stunde()
Stunde(«datum»)
|
{
{ |
Day | Returns the day number of the specified date or the current date if the argument is not specified. | Tag()
Tag(«datum»)
|
{
{ |
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.
|
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.
|
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»)
|
{ { |
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")
|
{ { { |
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. |
|
{
{ |
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.
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.
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»)
|
{
{ |
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.
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 | <a href="http://www.lutumtappert.de" target="_blank">Website</a> |