Table calculation functions (WIP)

Question 1

LOOKUP(expression, [offset])

Answer 1

Primary function

Returns the value of the expression in a target row,
specified as a relative offset from the current row.
If the offset is -1, then the result will be returned for
the previous value in the scope and direction

LOOKUP(SUM([Profit]), FIRST ()+2) computes the
SUM([Profit]) in the third row of the partition.

Question 2

TOTAL(expression)

Answer 2

Primary function

Returns the total for the given expression in the
current partition

TOTAL(SUM([Sales])) returns the total for the sum
of sales based on the current scope and direction.

Question 3

PREVIOUS_VALUE

Answer 3

Primary function

which returns the value of the expression in the previous row

Question 4

RUNNING_SUM, RUNNING_AVG and other similar running calculations

Answer 4

Primary functions

return the running sum, running average, etc. of the given expression, from the first row in the partition to the current row.

Question 5

WINDOW_AVG, WINDOW_STDEV, and other similar window calculations

Answer 5

Primary functions

return the average, standard deviation, etc. of the expression within the window (defined by means of offsets from the current row).

Question 6

RANK

Answer 6

Primary function

returns the standard competition rank for the current row in the partition with other options available that vary on how they process identical values.

Question 7

FIRST and LAST

Answer 7

Helper functions

return the number of rows from the current row to the first or last row in the partition.

Question 8

INDEX

Answer 8

Helper function

returns the position of the current value, given the chosen scope and direction.

Question 9

SIZE

Answer 9

Helper function

returns the number of rows in the current scope.