All the Questions in this page use the below sample Dataset. It is the Orders Data for a fictitious seller.

Order NumberOrder DateShipping DateProductPrice ($)Profit ($)
000101 Jan 2018 08:30:01 AM03 Jan 2018 11:21:05 AMABC800150
000202 Jan 2018 01:15:25 PM05 Jan 2018 05:30:15 PMXYZ600120
..................

ABSOLUTE

SynonymsABS
SyntaxABSOLUTE(number)
DescriptionReturns the absolute value of the Argument. The absolute value of a number is the number without its sign.
Argumentsnumber: The number for which the absolute value needs to be identified.
Examples
  • SUM(ABSOLUTE(PROFIT)) - Calculates the total of the absolute values of PROFIT from different Data-Rows.
  • ABSOLUTE(SUM(PRICE) - SUM(PROFIT)) - Returns the absolute value of the difference between Total PRICE and Total PROFIT.

PERCENTAGE

Synonyms-
SyntaxPERCENTAGE(number1, [number2])
DescriptionCalculates number1 as a percentage of number2.
Argumentsnumber1: First number in the Percentage.
number2: (Optional) Second number in the Percentage.
Examples
  • PERCENTAGE(PROFIT, PRICE) - Calculates 100*PROFIT/PRICE for each Data-Row and then returns an average of the result across all Data-Rows.
  • PERCENTAGE(PROFIT) - Calculates 100*PROFIT for each Data-Row and then returns an average of the result across all Data-Rows.
  • PERCENTAGE(SUM(PROFIT), OVERALL(SUM(PROFIT))) ACROSS PRODUCTS - Calculates the total PROFIT of each PRODUCT as a percentage of the overall PROFIT. See OVERALL.

PERCENTAGEINCREASE

Synonyms-
SyntaxPERCENTAGEINCREASE(number1, [number2])
DescriptionIf number2 is specified, returns the percentage-increase between the two arguments. Otherwise, returns the percentage-increase of number1 over time.
Argumentsnumber1: First number in the Percentage.
number2: (Optional) Second number in the Percentage.
Examples
  • PERCENTAGEINCREASE(PRICE, PROFIT) - Calculates 100*(PRICE - PROFIT)/PROFIT for each Data-Row and then returns an average of the result across all Data-Rows.
  • PERCENTAGEINCREASE(SUM(PROFIT)) ACROSS YEARS - For each year, returns the percentage-increase in the total PROFIT compared to the previous year.

STRINGCONCAT

Synonyms-
SyntaxSTRINGCONCAT(text1, [text2, ...])
DescriptionCombines multiple text values into a single text value. Each text value can be a Field, Function, Number or any other Text.
Argumentstext1: The first of the texts to combine.
text2: (Optional & Repeatable) Additional texts to combine.
Examples
  • PRICE IN STRINGCONCAT(CITY, :, DEPARTMENT) - Calculates the total PRICE across unique combinations of CITY and DEPARTMENT like "New York City:Clothing", "Los Angeles:Toys & Games", ...

IF

Synonyms-
SyntaxIF(comparison, measure1, [measure2])
DescriptionIf the comparison is TRUE, returns measure1. Otherwise, returns measure2.
Argumentscomparison: A logical comparison
measure1: The measure to return when the comparison is TRUE.
measure2: (Optional) The measure to return when the comparison is FALSE.
Examples
  • IF(PRICE > PROFIT, PRICE, PROFIT)
  • IF(PROFIT >= 0, PROFIT, 0)

LATEST

Synonyms-
SyntaxLATEST(measure, [dateField])
DescriptionReturns the Latest value for the measure. If dateField is not specified, uses the default Timestamp field.
Argumentsmeasure: The measure for which the latest value is needed.
dateField: (Optional) The Date field for which the latest value is taken.
Examples
  • LATEST(PRICE) OF ABC - Returns the most recent PRICE for the Product ABC
  • LATEST(ORDER NUMBER, SHIPPING DATE) - Returns the ORDER NUMBER for the most recently shipped item.

OVERALL

Synonyms-
SyntaxOVERALL(measure)
DescriptionComputes the measure using all the Data-Rows.
Argumentsmeasure: The measure to be computed.
Examples
  • PERCENTAGE(SUM(PROFIT), OVERALL(SUM(PROFIT))) ACROSS PRODUCTS - Calculates the total PROFIT of each PRODUCT as a percentage of the overall PROFIT.

CALCULATE

SynonymsCALC, COMPUTE, AGGREGATE, AGG
SyntaxCALC(number1, [number2, ...])
DescriptionNeeded as a wrapper to Arithmetic expressions. Behaves like SUM when multiple arguments are present.
Argumentsnumber1: The first number to compute.
number2: (Optional & Repeatable) Additional numbers to compute and add to number1.
Examples
  • CALCULATE(PRICE - PROFIT) ACROSS PRODUCTS - Calculates the total difference between PRICE and PROFIT for each PRODUCT.