NOTE: All the example Questions in this page use this Example Dataset.

COUNT

SynonymsNUMBER
SyntaxCOUNT(argument1, [argument2, ...])
DescriptionApproximate number of distinct occurences of the combination of Arguments.
NOTE: Use this for a better performance (faster results). It returns accurate results for low cardinality arguments (number of distinct values < 1000). For higher cardinality arguments, it returns results with a 5% error margin.
Argumentsargument1: First Argument in the combination.
argument2: (Optional & Repeatable) Additional Arguments.
Examples
  • COUNT(SALES) - Returns the number of distinct values of SALES
  • COUNT(SUM(SALES, PROFIT), PRODUCT(SALES, PROFIT)) - Evaluates these arguments at each Data-Row and returns the number of distinct combinations of the two values.
Special Cases: The function can behave differently if used in a row-level context:
  • SUM(PRODUCT(SALES, COUNT(SALES, PROFIT))) - Here, since the sibling Argument SALES is at row-level, the COUNT Function can only return a row-level value. In such cases, the COUNT Function returns the distinctness of the arguments within the row. In this example, it can return:
    • 2, if both arguments are non-empty and distinct
    • 1, if one of them is empty or both non-empty but have the same value
    • 0, if both are empty

EXACTCOUNT

SynonymsEXACT COUNT
SyntaxEXACTCOUNT(argument1, [argument2, ...])
DescriptionExact number of distinct occurences of the combination of Arguments.
NOTE: Please use the COUNT Function for a better performance (faster results). This function returns an accurate value at the cost of performance.
Argumentsargument1: First Argument in the combination.
argument2: (Optional & Repeatable) Additional Arguments.
Examples
  • EXACTCOUNT(SALES) - Returns the number of distinct values of SALES
  • EXACTCOUNT(SUM(SALES, PROFIT), PRODUCT(SALES, PROFIT)) - Evaluates these arguments at each Data-Row and returns the number of distinct combinations of the two values.
Special Cases: The function can behave differently if used in a row-level context:
  • SUM(PRODUCT(SALES, EXACTCOUNT(SALES, PROFIT))) - Here, since the sibling Argument SALES is at row-level, the EXACTCOUNT Function can only return a row-level value. In such cases, the EXACTCOUNT Function returns the distinctness of the arguments within the row. In this example, it can return:
    • 2, if both arguments are non-empty and distinct
    • 1, if one of them is empty or both non-empty but have the same value
    • 0, if both are empty

COUNTALL

SynonymsCOUNT ALL
SyntaxCOUNTALL(argument1, [argument2, ...])
DescriptionNumber of non-empty occurences of the combination of Arguments. (Doesn't check for distinctness.)
Argumentsargument1: First Argument in the combination.
argument2: (Optional & Repeatable) Additional Arguments.
Examples
  • COUNTALL(SALES) - Returns the number of rows in which SALES is non-empty.
  • COUNTALL(SALES, PROFIT) - Returns the number of rows in which at least one of the Arguments is non-empty.
Special Cases: The function can behave differently if used in a row-level context:
  • SUM(PRODUCT(SALES, COUNTALL(SALES, PROFIT))) - Here, since the sibling Argument SALES is at row-level, the COUNTALL Function can only return a row-level value. In such cases, the COUNTALL Function returns the non-emptiness of the arguments within the row. In this example, it can return:
    • 2, if both arguments are non-empty
    • 1, if one of them is empty
    • 0, if both are empty

MAX

SynonymsMAXIMUM
SyntaxMAX(number1, [number2, ...])
DescriptionMaximum of the Arguments.
Argumentsnumber1: First Argument.
number2: (Optional & Repeatable) Additional Arguments.
Examples
  • MAX(SALES) - Finds the Maximum value of SALES from the entire Data.
  • MAX(SALES, PROFIT) - Finds the Maximum of SALES and PROFIT values in the entire Data.
Special Cases: The function can behave differently if used in a row-level context:
  • AVG(PRODUCT(SALES, MAX(SALES, PROFIT))) - Here, since the sibling Argument SALES is at row-level, the MAX Function can only return a row-level value. In such cases, it will return the Maximum of arguments from that row.

MIN

SynonymsMINIMUM
SyntaxMIN(number1, [number2, ...])
DescriptionMinimum of the Arguments.
Argumentsnumber1: First Argument.
number2: (Optional & Repeatable) Additional Arguments.
Examples
  • MIN(SALES) - Finds the Minimum value of SALES from the entire Data.
  • MIN(SALES, PROFIT) - Finds the Minimum of SALES and PROFIT values in the entire Data.
Special Cases: The function can behave differently if used in a row-level context:
  • AVG(PRODUCT(SALES, MIN(SALES, PROFIT))) - Here, since the sibling Argument SALES is at row-level, the MIN Function can only return a row-level value. In such cases, it will return the Minimum of arguments from that row.

AVG

SynonymsAVERAGE, MEAN
SyntaxAVG(number1, [number2, ...])
DescriptionAverage/Mean of the Arguments.
Argumentsnumber1: First Argument.
number2: (Optional & Repeatable) Additional Arguments.
Examples
  • AVG(SALES) - Finds the Average value of SALES from the entire Data.
  • AVG(SALES, PROFIT) - Finds the Average of SALES and PROFIT values in the entire Data.
Special Cases: The function can behave differently if used in a row-level context:
  • SUM(PRODUCT(SALES, AVG(SALES, PROFIT))) - Here, since the sibling Argument SALES is at row-level, the AVG Function can only return a row-level value. In such cases, it will return the Average of arguments from that row.

WEIGHTEDAVERAGE

SynonymsWTAVG
SyntaxWEIGHTEDAVERAGE(value, weight)
DescriptionAverage of the first Argument weighted/scaled by the second.
Argumentsvalue: Number for which the Average needs to be calculated.
weight: (Optional) Number which adds weight to the value. If skipped, a weight of 1 is used.
Examples
  • WEIGHTEDAVERAGE(SALES, PROFIT) - Finds the Average of SALES values from the entire Data weighted by the corresponding PROFIT values.

STANDARDDEV

SynonymsSTDEV
SyntaxSTANDARDDEV(number)
DescriptionStandard Deviation. Quantifies the amount of variation of the values from the Mean.
Argumentsnumber: Number for which the Standard-Deviation needs to be calculated.
Examples
  • STANDARDDEV(SALES)
  • STANDARDDEV(SALES + PROFIT)

CUMULATIVESUM

SynonymsCUMULATIVE SUM
SyntaxCUMULATIVESUM(number)
DescriptionCumulative sum of the given Measure. The Measure needs to be Grouped across a Time Dimension.
Argumentsnumber: Numeric Measure for which the Cumulative Sum needs to be calculated.
Examples
  • CUMULATIVESUM(SUM(SALES)) ACROSS YEARS - Returns the cumulated sum of SALES at each Year.