A calculated KPI now supports window functions for calculations.
A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities.
First read about how window functions work:
https://www.postgresql.org/docs/9.1/tutorialwindow.html
 Go to Manage>KPIs & Dimensions and create a calculated KPI
 In the formula field you can now use window functions e.g.

SUM(SUM(clicks)) OVER (PARTITION BY 0)

 The OVER clause can either be left empty OVER () or look like 'OVER (PARTITION BY n)', where n is a nonnegative integer, meaning the window function will be computed over all but the last n dimensions.
 OVER () will compute the function over all records
 OVER (PARTITION BY 0) will compute the function differently for all distinct combinations of dimension values. When using aggregates as window functions, this will have the same effect as not computing the function at all (SUM(SUM(metric)) OVER () will yield the same results as SUM(metric))
 OVER (PARTITION BY 1) will compute the function for all distinct combinations of dimension values, excluding the last dimension. If dimensions are 'channel' and 'campaign', the function will be computed for every distinct value of 'channel'.
 Try the new metric in Explore!
EXAMPLE
In this example:
 2 dimensions are used in the table: GROUPS & Daily
 Metric Clicks with partition over 0: every row shows the same result as Clicks
 Metric Window over P 1: every row shows the result without considering the split from dimension Daily
 Metric Window over P 2: every row shows the result without considering the split from Dimension Daily & GROUPS, hence it shows the total result across all rows.
APPLICATION USECASE
Use the window function to compute the distance from the average cost.
 Define a custom KPI with the following formula:

(costs)  AVG(SUM(costs)) OVER (PARTITION BY 1).

Definition of window function taken from here: https://www.postgresql.org/docs/9.1/tutorialwindow.html
Comments
0 comments
Please sign in to leave a comment.