POP()

Use POP() for period-over-period calculations. POP() can be applied via a SQL API or SQL REST API request to dynamically add period-over-period calculations.

Current Period and Compare Period

Period over Period analysis is about comparing the same metric, calculated on two different time periods. We call these two time periods as the Current Period and the Compare Period.

As POP() is a time-based function, using time_agg is mandatory for using it.

Current Period

A single data point of a level of granularity that includes a time grain.

Examples:

  • "day" : 2025-01-01

  • "customer daily" : customer_id = 123, day = 2025-01-01

  • Customer rollup to nation and month: nation_name = US, month = 2025-03-01

Compare Period

Can be one or more data points, determined by the POP() function parameters skip_periods , compare_periods , the time_agg parameter direction and then aggregated using the value of the agg_function parameter.

Comparing Current Period and Comparison Period

Is done according to the value of pop_formula.

How POP() works

Lynk Functions, including POP() , are parsed and applied to the query after all the entities and features were parsed. See more details on this here.

You can think of POP() as a function that acts as follows:

"Given a dataset with a granularity level that includes a time_grain (day/week/month/etc), for each granularity level (row) take the metric as the current_period. Then move direction through time by time_grain: skip skip_periods periods, compare_periods times to collect the same metric, and aggregate them using agg_function to create the compare_period. Finally, apply pop_formula to calculate the relationship between current_period and compare_period."

POP() playground

Click the embedded link below to visit a visual POP() playground.

Lynk | POP Playgroundlynk-ai.github.io
POP visual playground - click to explore

In the image below you can see a visual example of how POP() works.

Period over Period visual representation

This example represents the following:

For each granularity level (day in this case) take the metric = sales as the current_period. Then move backward through time by time_grain: skip skip_periods = 7 periods compare_periods = 6 times to collect the same metric, and aggregate them using agg_function = p_50 to create the compare_period. Finally, apply pop_formula = absolute_difference to calculate the relationship between current_period and compare_period.

And it translates to the following SQL API:

// Some code
USE {
  "time_agg": {
    "time_grain": "day",
    "direction": "backward",
    "window_size": 1
  },
  "start_time": "2024-01-01",
  "stop_time": "2025-04-08"
}

SELECT  time_agg as date_day,
        POP(
          metric => 'measure(total_sales)',
          skip_periods => 7,
          compare_periods => 6,
          agg_function => 'p_50',
          pop_formula => 'absolute_difference'
        ) as pop_example
FROM    entity('customer')

time_grain

Type: String, taken from time_agg

Used for defining the period size. For example, choosing time_grain day, defines the period as day.

direction (from time_agg) [optional]

Used for defining the direction of the period over period calculation. Defaults to backward.

  • Choosing direction backward, will result by calculating the compare period over a set of datapoints that occurred before the datapoint we refer to. For example: Comparing total_sales of each month, to the average of total_sales on the previous 4 months.

  • Choosing direction forward, will result by calculating the compare period over a set of datapoints that occurred after the datapoint we refer to. For example: Comparing total_sales of each month, to the average of total_sales on the following 4 months.

window_size (from time_agg) [optional]

Used for defining the size of the cumulative window of the period over period calculation. Defaults to 1.

metric

Type: String

The metric feature or measure name of the main entity, to apply the POP (period over period) on.

skip_periods [optional]

Type: Numeric, defaults to 1.

Define how many datapoints to "jump over" when calculating the compare period.

For example: For comparing total_sales of each day, to the average of total_sales on the previous 4 same days of week, we can use skip_periods = 7 and compare_periods = 4 along with time_grain = day and direction = backward.

compare_periods [optional]

Type: Numeric, defaults to 1.

Define how many datapoints to take into include for the compare period. In other words, how many data points to include in the compare period.

For example: For comparing total_sales of each day, to the average of total_sales on the previous 4 same days of week, we can use skip_periods = 7 and compare_periods = 4 along with time_grain = day and direction = backward.

agg_function

Type: String.

The function to apply on the datapoints of the compare period.

You can use the following presets:

  • absolute_difference

  • relative_difference

  • percent_change

  • ratio

pop_formula

Type: String.

The function to apply on the current period and the compare period.

Preset formulas

  • avg

  • sum

  • min

  • max

  • count

  • p_<number> For example: p_50 for median

Custom formulas

Or define a custom function using the parameters current_period and compare_period

Example for custom pop_formula:

// custom pop_formula
    pop_formula => "({current_period} - {compare_period}) / 2"
PreviousLynk FunctionsNextContext

Last updated