Dashboard Functions Reference

The basic form calculates the average of a given database column (aggregation). The (M) form calculates the average of a given table column grouped by another field.

Syntax (Basic form)

AVG(<numeric field>)

Arguments

Syntax (Multipass form)

AVG(<group-by field>, <aggregation_fn>(<numeric field>))

Arguments

The basic form counts the number of unique values within the given values. The (M) form Returns the number of unique <aggregation> values from all aggregated <group-by-field> members.

Syntax (Basic form)

COUNT(<numeric field>)

Arguments

Syntax (Mutipass form)

COUNT( <group-by-field>, <aggregation> )

Arguments

Returns the actual item count of the given list of items, including duplicates.

Syntax

DUPCOUNT(<numeric field>)

Arguments

Returns the k-th largest value in a field.

Syntax

LARGEST(<numeric field>, <k>)

Arguments

The basic form returns the maximum value in a numeric field column. The (M) form returns the maximal <aggregation> value from all aggregated <group by field> members.

Syntax (Basic form)

MAX(<numeric field>)

Arguments

Syntax (Multipass form)

MAX(<group-by-field>, <aggregation>)

Arguments

Calculates the median of the given values. The median of a set of data is the middlemost number in the set. The median is also the number that is halfway into the set.

Syntax

MEDIAN(<numeric field>)

Arguments

The basic form returns the minimum value in a numeric field column. The (M) form returns the minimal <aggregation> value from all aggregated <group by field> members.

Syntax (Basic form)

MIN(<numeric field>)

Arguments

Syntax (Multipass form)

MIN(<group-by-field>, <aggregation>)

Arguments

Returns the most frequently occurring value from a column.

Syntax

MODE(<numeric field>)

Arguments

Calculates the percentage of the total.

Syntax

CONTRIBUTION(<numeric field>)

Arguments

The basic form returns the k-th percentile value from the given field, where k is any number between 0..1 (inclusive). The (M) form returns the k-th percentile value from the given numeric expression per <group by field>.

Syntax (Basic form)

PERCENTILE(<numeric field>, <k>)

Arguments

Syntax (Multipass form)

PERCENTILE(<group-by-field>, <aggregation>, <k>)

Arguments

The basic form returns the k-th quaetile value from the given field, where k is any number between 0..4 (inclusive). It can return minimum value, first quartile, second quartile, third quartile, and max value.

The (M) form returns the k-th percentile value from the given numeric expression per <group by field>.

Syntax (Basic form)

QUARTILE(<numeric field>, <k>)

Arguments

Syntax (Multipass form)

QUARTILE(<group-by-field>, <aggregation>), <k>)

Arguments

Returns the rank of a value in a list of values.

Rank function with a complicated formula that includes an ALL function may not yield results.

Syntax

RANK(<numeric field>, [DESC/ASC], [Rank Type], [<group-by field 1>,... , <group-by field n>])

Arguments

Limitation

When the Rank function is used in Pivot tables, it may skip ranks when used with Group By (Columns) due to the query limit. To fix this issue, increase your Pivot Query LIMIT (contact your CSM if you require assistance with this):

Returns the Standard Deviation of the given values (Population). Standard deviation is the square root of the average squared deviation from the mean. The standard deviation of a population gives researchers the amount of dispersion of data for an entire population.

Syntax

STDEVP(<numeric field>)

Arguments

Returns the Standard Deviation of the given values in a sample. Standard deviation is the square root of the unbiased average squared deviation from the mean. A standard deviation of a sample estimates the amount of dispersion in a given data set, based on a random sample.

Syntax

STDEV(<numeric field>)

Arguments

Returns the Variance of the given values (Population). Variance is the average squared deviation from the mean, based on an entire population of survey respondents.

Syntax

VARP(<numeric field>)

Arguments

Returns the Variance of the given values (Sample). Variance is the average squared deviation from the mean, based on a random sample of population of survey respondents.

Syntax

VARP(<numeric field>)

Arguments

The basic form returns the correlation coefficient of two numeric fields. The (M) form returns the correlation coefficient of two fields aggregations grouped by another field.

Syntax (Basic form)

CORREL(<numeric field a>, <numeric field b>)

Arguments

Syntax (Multipass form)

CORREL(<group-by field>, <aggregation a>, <aggregation b>)

Arguments

The basic form returns the population covariance of <numeric field a> and <numeric field b>. The (M) form returns the population covariance of two fields aggregations, grouped by another field.

Syntax (Basic form)

COVARP(<numeric field a>, <numeric field b>)

Arguments

Syntax (Multipass form)

COVARP(<group-by field>, <aggregation a>, <aggregation b>)

Arguments

The basic form returns the sample covariance of <numeric field a> and <numeric field b>. The (M) form returns the sample covariance of two fields aggregations, grouped by another field.

Syntax (Basic form)

COVAR(<numeric field a>, <numeric field b>)

Arguments

Syntax (Multipass form)

COVAR(<group-by field>, <aggregation a>, <aggregation b>)

Arguments

Returns the skewness of the distribution of a given value in the population.

Syntax

SKEWP(<numeric field>)

Arguments

Returns the skewness of the distribution of a given value in a sample.

Syntax

SKEW(<numeric field>)

Arguments

Returns the slope of a linear regression line through the provided series of x and y values.

Syntax

SLOPE(<numeric field a>, <numeric field b>)

Arguments

Returns the exponential distribution for a given value and a supplied distribution parameter mean.

Syntax

EXPONDIST(<numeric field>, <mean>, <cumulative (true/false)>)

Arguments

Returns the intercept of a linear regression line through the provided series of x and y values.

Syntax

INTERCEPT(<numeric not datetime>, <numeric field>)

Arguments

Returns the Normal distribution for a given value, a supplied distribution mean and standard deviation.

Syntax

NORMDIST(<numeric field>, <mean>, <standard deviation>, <cumulative (true/false)>)

Arguments

Returns the Poisson distribution for a given value and a supplied distribution mean.

Syntax

POISSONDIST(<numeric field>, <mean>, <cumulative (true/false)>)

Arguments

Returns Student's T-distribution for a given value and a supplied number of degrees of freedom.

Syntax

TDIST(<numeric field>, <degrees_freedom>, <cumulative (true/false)>)

Arguments

The basic form calculates the sum of a given database column (aggregation). The (M) form calculates the sum off a given table column grouped by another field.

Syntax (Basic form)

SUM(<numeric field>)

Arguments

Syntax (Multipass form)

SUM(<group-by field>, <aggregation>)

Arguments

Returns the absolute value of Returns the absolute value of its argument.

Mathematical Equivalent

f(x) = |x|

Syntax

ABS(<numeric field>)

Arguments

Returns the angle, in radians, whose cosine is the given numeric expression. Also referred to as arccosine, arcos, cos^-1.

Mathematical Equivalent

f(x) = cos^-1x

Syntax

ACOS(<numeric field>)

Arguments

Returns the angle, in radians, whose sine is the given numeric expression. Also referred to as arcsin, arsin, sine^-1.

Mathematical Equivalent

f(x) = sin^-1x

Syntax

ASIN(<numeric field>)

Arguments

Returns the angle in radians whose tangent is the given numeric expression. Also referred to as arctan, tan^-1.

Mathematical Equivalent

f(x) = tan^-1x

Syntax

ATAN(<numeric field>)

Arguments

Returns the smallest integer greater or equal to the argument.

Mathematical Equivalent

f(x) = ⌈x⌉

Syntax

CEILING(<numeric field>)

Arguments

Returns the trigonometric cosine of the given angle (in radians).

Mathematical Equivalent

f(x) = cos x

Syntax

COS(<numeric field>)

Arguments

Returns the cotangent of the argument, where the argument is in radians.

Mathematical Equivalent

f(x) = cot x

Syntax

COT(<numeric field>)

Arguments

Returns e (base of natural logarithms) raised to the power of the argument.

Mathematical Equivalent

f(x) = e^x

Syntax

EXP(<numeric field>)

Arguments

Returns the largest integer less than or equal to the argument.

Mathematical Equivalent

f(x) = ⌊x⌋

Syntax

FLOOR(<numeric field>)

Arguments

Returns the natural (base e) logarithm of the argument.

Mathematical Equivalent

f(x) = ln x = log_e x

Syntax

LN(<numeric field>)

Arguments

Returns the base 10 logarithm of the argument.

Mathematical Equivalent

f(x) = log₁₀ x

Syntax

LOG10(<numeric field>)

Arguments

Returns the remainder after a number is divided by a divisor. In this implementation of MOD, the sign of both arguments is ignored, treating them both as positive numbers.

Mathematical Equivalent

f(x) = x mod n where n is the divisor

Syntax

MOD(<numeric field>, <divisor>)

Arguments

Returns the results of the given value raised to a supplied power.

Mathematical Equivalent

f(x, a) = x^a

Syntax

Power(<numeric field>, <power>)

Arguments

Returns the integer portion of a division.

Mathematical Equivalent

f(x, a) = ⌊x/a⌋

Syntax

QUOTIENT(<numeric value>, <divisor>)

Arguments

Returns number rounded to a specified number of digits.

ROUND(<numeric field>, <num_digits>)

Arguments

Returns the sine of the argument, where the argument is in radians.

Mathematical Equivalent

f(x) = sin x

Syntax

SIN(<numeric field>)

Arguments

Returns the square root of the given value.

Syntax

SQRT(<numeric field>)

Arguments

Returns the tangent of the argument, where the argument is in radians.

Mathematical Equivalent

f(x) = tan x

Syntax

TAN(<numeric field>)

Arguments

Returns the hyperbolic cosine of the argument.

Mathematical Equivalent

f(x) = cosh x

Syntax

COSH(<numeric field>)

Arguments

Returns the hyperbolic sine of the argument.

Mathematical Equivalent

f(x) = sinh x

Syntax

SINH(<numeric field>)

Arguments

Returns the hyperbolic tangentof the argument.

Mathematical Equivalent

f(x) = tanh x

Syntax

TANH(<numeric field>)

Arguments

Returns the difference between <end time> and <start time> in seconds.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

SDIFF(<end time>, <start time>)

Arguments

Here is an example of using the SDIFF function to return the number of seconds between a create date and the update date:

Syntax

SDIFF(NOW(), <start time>)

Arguments

Returns the difference between <end time> and <start time> in minutes.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

MNDIFF(<end time>, <start time>)

Arguments

Here is an example of using the MNDIFF function to return the number of minutes between a create date and the update date:

Syntax

MNDIFF(NOW(), <start time>)

Arguments

Returns the difference between <end time> and <start time> in hours. Returns whole numbers.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

HDIFF(<end time>, <start time>)

Arguments

Here is an example of using the HDIFF function to return the number of hours between a create date and the update date:

Syntax

HDIFF(NOW(), <start time>)

Arguments

Returns the difference between <end time> and <start time> in days.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

DDiff(<end time>, <start time>)

Arguments

Here is an example of using the DDIFF function to return the number of days between a create date and the update date:

Syntax

DDiff(NOW(), <start time>)

Arguments

Returns the difference between <end time> and <start time> in months. Returns whole numbers.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

MDIFF(<end time>, <start time>)

Arguments

Here is an example of using the MDIFF function to return the number of months between a create date and the update date:

Syntax

MDIFF(NOW(), <start time>)

Arguments

Returns the difference between <end time> and <start time> in quarters. Returns whole numbers.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

QDIFF(<end time>, <start time>)

Arguments

Syntax

QDIFF(NOW(), <start time>)

Arguments

Returns the difference between <end time> and <start time> in years. Returns whole numbers.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

YDIFF(<end time>, <start time>)

Arguments

Syntax

YDIFF(NOW(), <start time>)

Arguments

Here is an example of the YDiff function being used with NOW():

Returns the difference between this week's data and the data from the previous week.

Use this function when the time resolution used in your widget is day or week. Otherwise, it does not display correct data.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

DIFFPASTWEEK(<numeric field>)

Arguments

Returns the difference between this month's data and the data from the previous month.

Use this function when the time resolution used in your widget is 'month'. Otherwise, it does not display correct data.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

DIFFPASTMONTH(<numeric field>)

Arguments

Returns the difference between this quarter's data and the data from the previous quarter.

Use this function when the time resolution used in your widget is 'month or 'quarter''. Otherwise, it does not display correct data.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

DIFFPASTQUARTER(<numeric field>)

Arguments

Returns the difference between this year's data and the data from the previous year. All time resolutions in the widget are available for this function (year, quarter, month, week, day).

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

DIFFPASTYEAR(<numeric field>)

Arguments

Returns the difference between this period's data and the data from the previous period.

Formula: (current value - compared value).

Accepts any time resolution (day, week, etc.).

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

DIFFPASTPERIOD(<numeric field>)

Arguments

Calculates growth over time. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.

Formula: (current value - compared value) / compared value.

Accepts any time resolution (day, week, etc.) in the widget.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

GROWTH(<numeric field>)

Arguments

Calculates growth over time. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.

Formula:(current value - compared value) / compared value.

Accepts any time resolution (day, week, etc.).

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

GROWTHRATE(<numeric field>)

Arguments

Calculates the growth from the past week to the current week. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.

Use this function when the time resolution in your widget is weeks or days. Otherwise, it does not display any data.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

GROWTHPASTWEEK(<numeric field>)

Arguments

Calculates the growth from the past month to the current month. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.
Use this function when the time resolution in your widget is month or day. Otherwise, it does not display any data.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

GROWTHPASTMONTH(<numeric field>)

Arguments

Calculates the growth from the past quarter to the current quarter. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.

Use this function when the time resolution in your widget is month or quarter. Otherwise, it does not display any data.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

GROWTHPASTQUARTER(<numeric field>)

Arguments

Calculates the growth from the past year to the current year. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.

Use this function when the time resolution in your widget is week, month, quarter, year.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

GROWTHPASTYEAR(<numeric field>)

Arguments

Returns the value for the previous day. Accepts the time resolution day.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

PASTDAY(<numeric field>, <Numeric of periods>)

Arguments

Returns the value for the same period in the previous week. Accepts the time resolutions day, week.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

ASTWEEK(<numeric field>, <number of periods>)

Arguments

Returns the value for the same period in the previous month. Accepts the time resolutions day, month.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

PASTMONTH(<numeric field>, <number of periods>)

Arguments

Returns the value for the same period in the previous quarter. Accepts the time resolutions day, month, quarter.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

PASTQUARTER(<numeric field>, <number of periods>)

Arguments

Returns the value for the same period in the previous year. Accepts any time resolution (day, week, etc.).

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

PASTYEAR(<numeric field>, <number of periods>)

Arguments

SELECT
  "datetime_months_dateshift_years_Date_res",
  "dTable_apastYearExampleLgAacsv_aProductName"."value" AS "ProductName_res",
  "sum__Revenue_res"
FROM
  (
    SELECT
      "t4"."datetime_months_dateshift_years_Date" AS "datetime_months_dateshift_years_Date_res",
      "t4"."ProductName",
      SUM("t4"."Revenue") AS "sum__Revenue_res"
    FROM
      (
        SELECT
          "t1"."datetime_months_dateshift_years_Date",
          "t1"."ProductName",
          "t1"."Revenue"
        FROM
          (
            SELECT
              CAST(
                ABACUS_CREATE_DATE(
                  EXTRACT(
                    YEAR
                    from
                      (
                        "apastYearExampleLgAacsv"."aDate" + INTERVAL '1' YEAR
                      )
                  ),
                  EXTRACT(
                    MONTH
                    from
                      (
                        "apastYearExampleLgAacsv"."aDate" + INTERVAL '1' YEAR
                      )
                  ),
                  1
                ) AS TIMESTAMP
              ) AS "datetime_months_dateshift_years_Date",
              "apastYearExampleLgAacsv"."aProductName" AS "ProductName",
              "apastYearExampleLgAacsv"."aRevenue" AS "Revenue"
            FROM
              "apastYearExample"."apastYearExampleLgAacsv"
            WHERE
              "apastYearExampleLgAacsv"."aProductName" IN (
                SELECT
                  "dTable_apastYearExampleLgAacsv_aProductName"."key"
                FROM
                  "apastYearExample"."dTable_apastYearExampleLgAacsv_aProductName"
                WHERE
                  "dTable_apastYearExampleLgAacsv_aProductName"."value" = 'A'
              )
          ) AS "t1"
          INNER JOIN (
            SELECT
              "t2"."Date_MonthDate"
            FROM
              (
                SELECT
                  "apastYearExampleLgAacsv0"."aDate_MonthDate" AS "Date_MonthDate"
                FROM
                  "apastYearExample"."apastYearExampleLgAacsv" AS "apastYearExampleLgAacsv0"
              ) AS "t2"
            GROUP BY
              "t2"."Date_MonthDate"
          ) AS "t3" ON "t1"."datetime_months_dateshift_years_Date" = "t3"."Date_MonthDate"
      ) AS "t4"
    GROUP BY
      "t4"."datetime_months_dateshift_years_Date",
      "t4"."ProductName"
  ) AS "it"
  INNER JOIN "apastYearExample"."dTable_apastYearExampleLgAacsv_aProductName" ON "it"."ProductName" = "dTable_apastYearExampleLgAacsv_aProductName"."key"
ORDER BY
  "it"."datetime_months_dateshift_years_Date_res",
  "dTable_apastYearExampleLgAacsv_aProductName"."value"

SELECT
  "Date_MonthDate_res",
  "dTable_apastYearExampleLgAacsv_aProductName"."value" AS "ProductName_res",
  "uniform_sum__Revenue_res"
FROM
  (
    SELECT
      "t1"."Date_MonthDate" AS "Date_MonthDate_res",
      "t1"."ProductName",
      "t8"."sum__Revenue" AS "uniform_sum__Revenue_res"
    FROM
      (
        SELECT
          "t0"."Date_MonthDate",
          "t0"."ProductName"
        FROM
          (
            SELECT
              "apastYearExampleLgAacsv"."aDate_MonthDate" AS "Date_MonthDate",
              "apastYearExampleLgAacsv"."aProductName" AS "ProductName"
            FROM
              "apastYearExample"."apastYearExampleLgAacsv"
            WHERE
              "apastYearExampleLgAacsv"."aProductName" IN (
                SELECT
                  "dTable_apastYearExampleLgAacsv_aProductName"."key"
                FROM
                  "apastYearExample"."dTable_apastYearExampleLgAacsv_aProductName"
                WHERE
                  "dTable_apastYearExampleLgAacsv_aProductName"."value" = 'A'
              )
          ) AS "t0"
        GROUP BY
          "t0"."Date_MonthDate",
          "t0"."ProductName"
      ) AS "t1"
      INNER JOIN (
        SELECT
          "t7"."datetime_months_dateshift_years_Date",
          SUM("t7"."Revenue") AS "sum__Revenue"
        FROM
          (
            SELECT
              "t4"."datetime_months_dateshift_years_Date",
              "t4"."Revenue"
            FROM
              (
                SELECT
                  CAST(
                    ABACUS_CREATE_DATE(
                      EXTRACT(
                        YEAR
                        from
                          (
                            "apastYearExampleLgAacsv0"."aDate" + INTERVAL '1' YEAR
                          )
                      ),
                      EXTRACT(
                        MONTH
                        from
                          (
                            "apastYearExampleLgAacsv0"."aDate" + INTERVAL '1' YEAR
                          )
                      ),
                      1
                    ) AS TIMESTAMP
                  ) AS "datetime_months_dateshift_years_Date",
                  "apastYearExampleLgAacsv0"."aProductName" AS "ProductName",
                  "apastYearExampleLgAacsv0"."aRevenue" AS "Revenue"
                FROM
                  "apastYearExample"."apastYearExampleLgAacsv" AS "apastYearExampleLgAacsv0"
                WHERE
                  "apastYearExampleLgAacsv0"."aProductName" IN (
                    SELECT
                      "dTable_apastYearExampleLgAacsv_aProductName"."key"
                    FROM
                      "apastYearExample"."dTable_apastYearExampleLgAacsv_aProductName"
                    WHERE
                      "dTable_apastYearExampleLgAacsv_aProductName"."value" = 'A'
                  )
              ) AS "t4"
              INNER JOIN (
                SELECT
                  "t5"."Date_MonthDate"
                FROM
                  (
                    SELECT
                      "apastYearExampleLgAacsv1"."aDate_MonthDate" AS "Date_MonthDate"
                    FROM
                      "apastYearExample"."apastYearExampleLgAacsv" AS "apastYearExampleLgAacsv1"
                  ) AS "t5"
                GROUP BY
                  "t5"."Date_MonthDate"
              ) AS "t6" ON "t4"."datetime_months_dateshift_years_Date" = "t6"."Date_MonthDate"
          ) AS "t7"
        GROUP BY
          "t7"."datetime_months_dateshift_years_Date"
      ) AS "t8" ON "t1"."Date_MonthDate" = "t8"."datetime_months_dateshift_years_Date"
  ) AS "it"
  INNER JOIN "apastYearExample"."dTable_apastYearExampleLgAacsv_aProductName" ON "it"."ProductName" = "dTable_apastYearExampleLgAacsv_aProductName"."key"
ORDER BY
  "it"."Date_MonthDate_res",
  "dTable_apastYearExampleLgAacsv_aProductName"."value"

Returns the running average starting from the beginning of the week up to the current day. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the active time resolution is years, quarters, or months.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

WTDAVG(<numeric field>)

Arguments

Returns the running total starting from the beginning of the week up to the current day or week. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the active time resolution is years, quarters, or months.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

WTDSUM(<numeric field>)

Arguments

Returns the running average starting from the beginning of the month up to the current day. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Use this function when the active time resolution in your widget is 'days'. Returns null if the active time resolution is quarters or years or weeks.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

MTDAVG(<numeric field>)

Arguments

Returns the running total starting from the beginning of the month up to the current day. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Use this function when the active time resolution in your widget is 'days'. Returns null if the active time resolution is quarters or years or weeks.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

MTDSUM(<numeric field>)

Arguments

Returns the running average starting from the beginning of the quarter up to the current day or month. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the active time resolution is weeks.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

QTDAVG(<numeric field>)

Arguments

Returns the running total starting from the beginning of the quarter up to the current day or month. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the active time resolution is weeks.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

QTDSUM(<numeric field>)

Arguments

Returns the running average starting from the beginning of the year up to the current day, week, month, quarter or year. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the query is invalid or returns no result.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

YTDAVG(<numeric field>)

Arguments

Returns the running total starting from the beginning of the year up to the current day, week, month, quarter or year. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the query is invalid or returns no result.

The active time resolution is determined by the minimum date level of the date dimension used for display and filtering. If there is no active time resolution, the formula fails. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. When compatibility mode (old translator) is used, it returns the current value and does not calculate the time difference.

Syntax

YTDSUM(<numeric field>)

Arguments

Changes the scope of a measure calculation to ignore a dimension being used in the widget for presentation or as a filter.

This function can only work as part of a measured value, not by itself.

Syntax

ALL(<any data type>)

Arguments

Here is an example of a widget using the All function to ignore the grouping by Date filtering:

Here is an example of a widget using the All function to ignore grouping according to the values in the Age Range, (a text column):

Returns the Time period Member in <time field> which is N periods back from the current member.

This function can only work as part of a measured value, not by itself.

This function works will all time resolutions. However, en sure that the active time resolution in the widget matches the time resolution in the function.

Syntax

((<numeric field>), PREV(<time field>, <N>))

Arguments

In the following example, the first column is the date (grouped by year), the next 2 columns are different ways to get the same total revenue for each year, column 4 uses Prev to return the values from the previous year, and column 5 uses Prev with the optional field to return the values from 2 years prior:

Here, PREV overrides the default yearly group-by filtering for the Revenue, and causes the Revenue to be filtered according to the previous year, (or earlier, based on the optional field).

Returns the value for the time-period member in <time field> which is N periods after the current member.

This function can only work as part of a measured value, not by itself.

This function works will all time resolutions. However, make sure that the active time resolution in the widget matches the time resolution in the function.

Syntax

((<numeric field>), NEXT(<time field>, <N>))

Arguments

In the following example, the first column is the date (grouped by year), the next 2 columns are different ways to get the same total revenue for each year. Column 4 uses Next to return the values from the following year, and column 5 uses Next with the optional field to return the values for 2 years later:

Here, NEXT overrides the default yearly group-by filtering for the Revenue, and causes the Revenue to be filtered according to the following year, (or later, based on the optional field).

Returns the value for the current time period. The NOW function receives a date dimension and its level and returns all the members in that dimension which match the current query execution time.

This function can only work as part of a measured value, not by itself.

Use this function when the time resolution in your widget is day, month, quarter, year.

Syntax

((<numeric field>), NOW(<time field>))

Arguments

Here is an example of a widget using the Now function to only return the values for the current year, (2023):

Returns the <result expression> of the first <condition> that evaluates as true. If no condition is true, the <result expression> for the ELSE is returned, (if defined).

Syntax

(WHEN <condition> THEN <result expression> [...] [ELSE <result expression>] END)

Arguments

Returns <numeric expression 1> when the <condition> is true, and it returns <numeric expression 2> when the <condition> is false. Nested conditional statements are supported.

Syntax

IF(<condition>, <numeric expression 1>, <numeric expression 2>)

Arguments

Here is an example of a widget that uses the If function. In column 3 it indicates that the number of sales (revenue records) for Brand ID 1 is less than 4 by displaying the numeric value "0". In column 4 it indicates that the number of sales*2 (which equals 6) is greater than 4 by displaying the numeric value "999":

Returns true if the expression does not contain data (Null), where the dimensions used in the query for presentation return any data.

Syntax

ISNULL(<numeric field>)

Arguments

When the NOW function is used without any arguments it returns the query execution time (according to the Sisense server).

The NOW function can also effectively act as the Date parameter in the DATEDIFF functions (Day Difference, Hour Difference, Minutes Difference, Month Difference, Quarter Difference, Second Difference, Year Difference). See how to use these functions in the Dashboard Functions Reference section above.

Syntax

NOW()

For example, here is the NOW() function being used by the YDIFFfunction:

Returns the running total of the measure by the defined dimension according to the current sorting order in the widget.

By default, RSUM accumulates a measure by the sorting order of the dimension. To accumulate by another order, the relevant measure should be added as an additional column and sorted.

Syntax

RSUM (<numeric field>),

RSUM (<numeric field>, <continuous>)

Arguments

Limitations

• Running Sum is not supported with Measured Values. This means that filters will have to be applied on the widget.

Returns the numeric order position of rows sorted into ascending or descending order, breaking ties with further arguments.

The expressions must be aggregated by applying the MIN/MAX functions.

Syntax

ORDERING(<expression1>, <expression2>)

Arguments

Returns a numeric result for a given R expression and a list of numeric values.

The R expression is passed to the running Rserve.

Syntax

{use code block} RDOUBLE(<R expression>, [<ordering>], <numeric value 1>, [<numeric value 2>, ..., <numeric value n>])

RDOUBLE(<recycle>, <R expression>, [<ordering>], <numeric value 1>, [<numeric value 2>, ..., <numeric value n>])

Argument

For additional discussion on using RDouble and how to do advanced forecasting with R, see here.

Returns an integer result for a given R expression and a list of numeric values.

The R expression is passed to the running Rserve.

Syntax

{use code block} RINT(<R expression>, [<Ordering>], <numeric value 1>, [<numeric value 2>, ..., <numeric value n>])

RINT(<recycle>, [<Ordering>], <R expression>, <numeric value 1>, [<numeric value 2>, ..., <numeric value n>])

Arguments

For additional discussion on using RInt and how R works with Sisense, see here.

Returns the requested number of left-most characters of an expression.

Syntax

Left(<Expression>,<length>)

Arguments

Returns the requested number of right-most characters of an expression.

Syntax

Right(<Expression>,<length>)

Arguments

Concatenates (combines) two or more expressions.

Syntax

Concat(<Expression 1>,<Expression 2>[, <Expression N>...])

Arguments