Angular Embedded AnalyticsCalculations

Version 3.0.0

Calculations can be created in AG Studio alongside those the developer provides.

In the example below, a Calculated Column (Total Medals) and a Measure (Total Points) have already been created in the same way you would.

To add one, open the menu in the title bar of a data source section in the Data Panel and choose:

  • New Calculated Column for a calculation that returns a value for each row, for example Sales[Revenue] - Sales[Cost].
  • New Measure for a calculation that returns a single value for the rows it covers, for example SUM(Sales[Revenue]).

The new field is added to that data source's section and opens in the Edit Panel ready to be edited. Give it a name, write its expression, and choose the format its values should be displayed in.

Which of the two you choose describes what your expression returns, so it is worth being deliberate about it. A Calculated Column is evaluated row by row and can have its aggregation changed when it is used in a widget. A Measure is a single value for whatever rows it covers and cannot be aggregated further. AG Studio does not check that your expression matches the type you picked, because the result is only known once the query runs, so a mismatch shows up as unexpected values in a widget rather than as an error in the panel. For how your developer defines the two, see Data Setup.

Fields you create are always fully editable. Unlike fields provided by your developer, you can also change their format type and remove them: select the field in the Data Panel, then Edit to change it or Delete to remove it. Deleting a field cannot be undone.

Writing Expressions Copy Link

The Expression input appears only for Calculated Columns and Measures you created yourself. It offers autocomplete for functions and fields, matches brackets, and highlights syntax errors as you type. Expressions are not case sensitive: function names, TRUE and FALSE, and the data source and field names in a reference all match whatever case you type.

An expression with an error is still saved, but the field produces no values until the error is fixed.

DescriptionSyntax
Field referencesMedals[Gold], [Total Medals], 'Completed Orders'[Date]
Strings"string" (double quotes only)
Numbers123, 1.23, 1e3
BooleansTRUE, FALSE
Arithmetic operators+, -, *, /, ^
Brackets3 * (2 + 1)
Comparison operators>, >=, <, <=, =, == (alias for =), <> (not equal)
Boolean operatorsNOT x, &&, ||
String concatenationa & b
Function callsADD(a, b)
Comments-- Single Line, // Single Line, /* Multi Line */

Set the Format to suit the values the expression returns. An expression returning a number can be shown as Integer or Decimal, but not as Text. Widgets using a field whose format does not match its values may fail to display data.

Functions Copy Link

The functions below are grouped by the kind of value they work with.

Aggregation Copy Link

A Measure returns one value for the rows it covers, so its expression combines those rows with an aggregation function. SUM(Sales[Revenue]) totals the revenue of every row the Measure covers, and 3 * SUM(Medals[Gold]) + SUM(Medals[Silver]) combines two totals. These are the same aggregations available when a field is used in a widget, described in Using Data.

FunctionDescription
SUM(field)Adds all values together.
AVG(field)The mean of all values.
MIN(field)The smallest value.
MAX(field)The largest value.
COUNT(field)The number of values that are not empty.
COUNTD(field)The number of distinct values.
FIRST(field)The first value, based on the current data order.
LAST(field)The last value, based on the current data order.

Comparison Copy Link

These functions compare two values, or test whether a value appears in a list, and return true or false.

FunctionDescription
EQUALS(a, b)Are the two values equal?
NOTEQUAL(a, b)Are the two values not equal?
LESSTHAN(a, b)Is the first value less than the second?
GREATERTHAN(a, b)Is the first value greater than the second?
LESSTHANOREQUAL(a, b)Is the first value less than or equal to the second?
GREATERTHANOREQUAL(a, b)Is the first value greater than or equal to the second?
IN(value, ...)Is the first value one of the values that follow?

Logic Copy Link

These functions combine or invert true and false values, and test whether a value is empty.

FunctionDescription
IF(test, then, else)Returns the second value when the first is true, otherwise the third.
AND(a, b)Are both values true?
OR(a, b)Is either value true?
NOT(value)Reverses a true or false value.
ISTRUE(value)Is the value true?
ISFALSE(value)Is the value false?
ISNULL(value)Is the value empty?
ISNOTNULL(value)Is the value not empty?

Maths Copy Link

These functions perform arithmetic on numbers, including rounding, powers and logarithms.

FunctionDescription
ADD(a, b)Adds two numbers, or joins two pieces of text.
SUBTRACT(a, b)Subtracts the second number from the first.
MULTIPLY(a, b)Multiplies two numbers.
DIVIDE(a, b)Divides the first number by the second.
MODULO(a, b)Remainder of the first number divided by the second.
MOD(a, b)Remainder of the first number divided by the second.
NEGATE(value)Reverses the sign of a number.
ABS(value)The value of a number without its sign.
SIGN(value)The sign of a number, as -1, 0 or 1.
FLOOR(value)Rounds down to the nearest whole number.
CEILING(value)Rounds up to the nearest whole number.
ROUND(value, places?)Rounds to the nearest whole number, or to the given decimal places.
TRUNCATE(value, places?)Drops the decimals, or all but the given decimal places.
POWER(base, exponent)Raises the first number to the power of the second.
EXP(value)Raises e to the power of a number.
LN(value)Natural logarithm of a number.
LOG(base, value)Logarithm of the second number to the base of the first.
LOG10(value)Base 10 logarithm of a number.
GREATEST(a, b, ...)The largest of two or more numbers.
LEAST(a, b, ...)The smallest of two or more numbers.

Trigonometry Copy Link

Angles are in radians.

FunctionDescription
SIN(value)Sine of a number.
COS(value)Cosine of a number.
TAN(value)Tangent of a number.
ASIN(value)Inverse sine of a number.
ACOS(value)Inverse cosine of a number.
ATAN(value)Inverse tangent of a number.
ATAN2(a, b)Inverse tangent of the quotient of two numbers.

Text Copy Link

These functions transform text, extract part of it, or match it against a pattern.

FunctionDescription
UPPER(text)Converts text to upper case.
LOWER(text)Converts text to lower case.
SUBSTRING(text, from, length?)Part of a piece of text, starting at a position.
POSITION(needle, haystack)Position of the first occurrence of one piece of text within another.
LTRIM(text, characters)Removes the given characters from the start of a piece of text.
RTRIM(text, characters)Removes the given characters from the end of a piece of text.
BTRIM(text, characters)Removes the given characters from both ends of a piece of text.
OVERLAY(text, replacement, from, length?)Replaces part of a piece of text with another.
LIKE(text, pattern, escape?)Wildcard match, where % matches any characters and _ matches one.

Date Copy Link

The unit is the first argument, given as text - for example "day", "month" or "year".

FunctionDescription
DATEADD(unit, date, amount)Adds a number of units to a date.
DATEDIFF(unit, from, to)The number of units between two dates.
DATETRUNC(unit, date)The start of the period a date falls in.
DATEEND(unit, date)The end of the period a date falls in.
DATEEXTRACT(unit, date)A single component of a date, such as its year, as a number.
DATEFROMPARTS(year, month, day)Builds a date from year, month and day numbers.
CURRENTDATE()Today's date. The same value for every row in a query.
CURRENTTIMESTAMP()The current date and time. The same value for every row in a query.

Statistics Copy Link

These functions summarise the distribution of a field across the rows it covers.

FunctionDescription
MEDIAN(field)The middle value of the field across the rows covered.
PERCENTILE(field, p)The value at percentile p, between 0 and 1, of the field.
PERCENTILES(field, p, ...)Several percentiles of the field at once.