Arcadia Enterprise supports several analytic functions that examine overlapping groupings of data.
Analytic functions are similar to aggregate functions because both use the contents of
multiple data input rows to calculate the result. Analytic functions use flexible conditions
that are specified by the OVER(...)
clause to order and group input so that
specific rows may be part of the calculation for several output values.
This article contains the following sections:
Syntax for analytic functions is slightly different depending on the type of data connection used.
Supported Data Connections include the following:
The Analytic Functions field properties are not available on MariaDB, MySQL, non-Oracle SQLite, Apache Drill, and Apache Solr connections
Syntax for analytic functions is slightly different depending on the type of data connection used. Analytic functions are not available for connections to MySQL, SQLite, Drill, MS Sql Server, Teradata, Solr, KSql, and MariaDB.
WHERE
and
GROUP BY
clauses. Therefore, the function excludes the rows that are
filtered out by these mechanisms, and they never become part of the analytic function data
subset.The rest of this topic covers the basic steps of accessing the analytic functions, and the basic visual set-up that we use in subsequent topics, to demonstrate each analytic function.
To use analytic functions, follow these steps.
Open the visual where you want to specify an analytic function, in Edit mode.
In this section, we use a line visual defined in the Basic Visual for Aggregates example.
On a measurement shelf of a visual, click the field you plan to modify to open the Field Properties menu.
In our examples, we use the population field on the X Axis shelf.
In the Field Properties menu, click to expand the Analytic Functions menu.
Select one of the following analytic functions, directly supported by Arcadia Enterprise:
In addition to these, you may use the expression builder to specify other analytic functions. See Enter or Edit Expression.
Here, we create a basic line visual on the World Life Expectancy
dataset,
through an Arcadia data connection. In subsequent topics, we use this visual to demonstrate
how the various analytic functions for aggregates work.
Populate the shelves of the visual from the fields listed in the Data menu:
Add the field year
. Order it in
ascending order.
Add the field population
.
Add the filed country
.
Order it in ascending order.
Add the field year
, and set it to the
interval of 1950 through 2010.
Add the field
un_subregion
, and set it to Northern Africa.
Click Refresh Visual to see the new line visual.
Here, we create a basic table visual on the World Life Expectancy
dataset,
through an Arcadia data connection. In subsequent topics, we use this visual to demonstrate
how the various analytic functions for single values work.
Populate the shelves of the visual from the fields listed in the Data menu:
Add the fields country
,
life_expectancy
, gdp_per_capita
, and
population
.
Add the field year
, and select the value
2010.
Add the field un_subregion
, and set it to
Africa.
life_expectancy
.[Optional] In the Enter/Edit Expression editor, change the
gdp_per_capita
calculation on the shelf, and rename it:
[gdp_per_capita]*[population] as 'GDP'
Click Refresh Visual to update the table visual.