Supported Table Calculations

Table calculations are a group of functions that provide context in an analysis and can help you create enriched aggregated analyses. A table calculation function performs operations on the data to reveal relationships between fields. For example, you can calculate percentage of total, running sum, difference, common baseline, and rank.

Lookup-Based Functions

  • Difference calculates the difference between a measure based on one set of partitions and sorts, and a measure based on another.
  • Lag calculates the lag (previous) value for a measure.
  • Lead calculates the lead (following) value for a measure.
  • percentDifference calculates the percentage difference between the current value and a comparison value.

Over Functions

  • avgOver calculates the average of a measure over one or more dimensions.
  • countOver calculates the count of a field over one or more dimensions.
  • distinctCountOver calculates the distinct count of the operand partitioned by the specified attributes at a specified level.
  • maxOver calculates the maximum of a measure over one or more dimensions.
  • minOver calculates the minimum of a measure over one or more dimensions.
  • percentileOver calculates the nth percentile of a measure partitioned by a list of dimensions.
  • percentileContOver calculates the nth percentile based on a continuous distribution of the numbers of a measure partitioned by a list of dimensions.
  • percentileDiscOver calculates the nth percentile based on the actual numbers of a measure partitioned by a list of dimensions.
  • percentOfTotal calculates the percentage that a measure contributes to the total.
  • periodOverPeriodDifference calculates the difference of a measure over two different time periods as specified by period granularity and offset.
  • periodOverPeriodLastValue calculates the last (previous) value of a measure from a previous time period as specified by period granularity and offset.
  • periodOverPeriodPercentDifference calculates the percent difference of a measure over two different time periods as specified by period granularity and offset.
  • periodToDateAvgOverTime calculates the average of a measure for a given time granularity, such as a quarter, up to a point in time.
  • periodToDateCountOverTime calculates the count of a dimension or measure for a given time granularity, such as a quarter, up to a point in time.
  • periodToDateMaxOverTime calculates the maximum of a measure or date for a given time granularity, such as a quarter, up to a point in time.
  • periodToDateMinOverTime calculates the minimum of a measure or date for a given time granularity, such as a quarter, up to a point in time.
  • periodToDateSumOverTime calculates the sum of a measure for a given time granularity, such as a quarter, up to a point in time.
  • sumOver calculates the sum of a measure over one or more dimensions.
  • stdevOver calculates the standard deviation of the specified measure, partitioned by the specified attribute or attributes, based on a sample.
  • stdevpOver calculates the standard deviation of the specified measure, partitioned by the chosen attribute or attributes, based on a biased population.
  • varOver calculates the variance of the specified measure, partitioned by the chosen attribute or attributes, based on a sample.
  • varpOver calculates the variance of the specified measure, partitioned by the chosen attribute or attributes, based on a biased population.

Ranking Functions

  • Rank calculates the rank of a measure or a dimension.
  • denseRank calculates the rank of a measure or a dimension, ignoring duplicates.
  • percentileRank calculates the rank of a measure or a dimension, based on percentile.

Running Functions

  • runningAvg calculates a running average for a measure.
  • runningCount calculates a running count for a measure.
  • runningMax calculates a running maximum for a measure.
  • runningMin calculates a running minimum for a measure.
  • runningSum calculates a running sum for a measure.

Window Functions

  • firstValue calculates the first value of the aggregated measure or dimension partitioned and sorted by specified attributes.
  • lastValue calculates the last value of the aggregated measure or dimension partitioned and sorted by specified attributes.
  • windowAvg calculates the average of the aggregated measure in a custom window that is partitioned and sorted by specified attributes.
  • windowCount calculates the count of the aggregated measure in a custom window that is partitioned and sorted by specified attributes.
  • windowMax calculates the maximum of the aggregated measure in a custom window that is partitioned and sorted by specified attributes.
  • windowMin calculates the minimum of the aggregated measure in a custom window that is partitioned and sorted by specified attributes.
  • windowSum calculates the sum of the aggregated measure in a custom window that is partitioned and sorted by specified attributes.

Parent topic: Supported Functions