Turn Complex Analytics into Function Calls: Using HENGSHI SENSE Advanced Calculation Functions Correctly

Abstract: Retention, cumulative totals, same-period comparisons, partitioned summaries, and conditional aggregation may each be a simple business request, yet their queries involve time axes, chart grouping, filters, and data sources. HENGSHI SENSE provides advanced calculation functions, aggregate functions, and advanced chart calculations for these common tasks. Functions do not replace business definitions, but can encode objects, granularity, and filtering rules in reusable metric expressions. Using the 6.2 function manual as its semantic baseline and the published 6.3.2 and 6.2.5 release notes as references, this article explains usage and boundaries.

1 Define the context before writing a function

Advanced calculations are not arbitrary SQL fragments. Time functions depend on chart time dimensions, pre-calculation functions interact with chart grouping and subtotals, and aggregate functions determine how input rows are reduced. Before defining a metric, specify the analytical object, underlying events, statistical granularity, time endpoints, and whether conditions come from page filters or the metric itself. Teams can then determine whether a metric still answers the same question when charts, dimensions, or subtotals change.

The expressions below use teaching fields adapted to official function semantics. Replace {user_id}, {event_time}, and other fields with actual model fields and use small samples to verify output granularity, filters, and display formatting. These teaching expressions are not presented as production-tested results or performance validation. The function documentation does not provide a comprehensive matrix guaranteeing full support for every advanced function on every data source; field types, date expressions, connection types, and function combinations must be validated in the target environment.

2 Retention and continuous activity: the date axis and endpoints are essential

retention(expression, date expression, lowExpr, upperExpr, ...) returns retained or active object counts under a time dimension. The date expression must appear as a chart dimension and, when used in summarize, as a non-aggregate argument. upperExpr must be one day later than the business interval’s actual end. Four optional arguments specify inclusion/exclusion conditions; all four must appear together in a fixed order. The output is a count, not automatically a retention rate: the denominator must also be defined by the metric.

retention({user_id}, day({event_time}),
  add_day(day({event_time}), 1),
  add_day(day({event_time}), 8))

continuous_retention(expression, date expression, lowExpr, upperExpr, period) returns continuously active counts. period supports only day, week, month, quarter, and year, with the same date-axis and end-point rules. It is stricter than appearing once within a window, so user identifiers, deduplication rules, and activity events must be defined first. A continuous activity rate also requires an explicit denominator; changing display formatting alone is insufficient.

static_retention, static_retention_rate, and static_continuous_retention suit output grouped by non-time dimensions such as channels or product lines. “Static” refers to non-time output grouping, not the absence of dates: the functions still require a date expression, a calculation interval, and a comparison interval. Both end dates must be one day later than the actual end dates, and the continuous static function also needs period.

static_retention({user_id}, {event_time},
  '2026-01-01', '2026-02-01',
  '2026-02-01', '2026-03-01')

3 Accumulation, comparison, and moving calculations: signatures determine time semantics

date_accumulate(function1(function2(expression)), date expression, reset period) first performs a base aggregation and then accumulates along the time series. The outer function can be sum, avg, min, or max; the inner function additionally supports percentile, count, and distinct_count. reset period can be hour, day, week, month, quarter, or year. This function is available only for creating metrics or API queries, and requires the date expression in chart or summarize grouping. Monthly, quarterly, and yearly accumulation differ by reset period; financial periods or non-calendar weeks should first be defined in the date model.

date_accumulate(sum(sum({sales_amount})),
  day({order_time}), 'month')

date_compare(function(expression), date expression1, date expression2) obtains comparison values using explicit date expressions. previous(function(expression), period, delta) calculates along the chart’s time axis; period can be year, quarter, month, week, day, or hour, and defaults to the chart’s smallest time granularity when omitted. The manual’s example returns null for discontinuous dates, so previous is not synonymous with “the previous row.”

date_shift(function1(function2(expression)), date expression, lowExpr, upperExpr) shifts item by item along the time series, using numerical backward and forward offsets for moving statistics. The manual does not define it as a calendar window that automatically fills missing dates; when there are gaps, check the current item and boundary results. Measure performance using representative data, grouping cardinality, concurrency, and connection settings; fixed improvements cannot be promised.

4 Pre-calculation, cross-dataset lookup, and partial summaries: specify grouping explicitly

Function	Semantics
calculate(ARG1, [ARG2], ...)	Pre-calculated values change with subtotals. ARG2 is optional; when supplied, it removes that field from chart grouping.
calculatep(ARG1, [ARG2], ...)	Pre-calculated values are unaffected by subtotals. ARG2 likewise removes specified chart grouping fields.
calculatex(ARG1, ARG2, ...)	Not controlled by dimensions and usable as a dimension. ARG2 is required and must explicitly specify the grouping needed for pre-calculation.

All three calculate expressions before those values participate in chart calculations, commonly for new metrics or chart filters. When used as metrics, they also need a suitable outer aggregate function. calculatex is not a zero-argument instruction to ignore all dimensions. For rankings, shares, and overall denominators, check detail, grouped, and subtotal rows separately.

calculatex(rank() OVER (ORDER BY sum({sales_amount}) ASC),
  {category}) < 4

lookupvalue(ARG1, ARG2, ARG3) is available only for creating metrics. ARG1 is an aggregate expression on an unrelated dataset, and ARG2 is that dataset’s matching expression; fields in both must include dataset IDs. ARG3 is the current chart’s matching expression and must be identical to one chart dimension. This is not a single-value VLOOKUP for arbitrary raw fields. Ensure ARG1 yields a business-unique, explainable aggregate at ARG2’s granularity, and verify duplicate keys and unmatched values.

lookupvalue(sum({{2}}.{purchase_amount}),
  year({{2}}.{purchase_date}), year({order_date}))

In rollup_value(ARG) OVER ([PARTITION BY expr1]), ARG is an aggregate expression. OVER() produces an overall summary, while PARTITION BY summarizes by a subset of dimensions; the documented signature contains no ordering or row window. It can provide denominators such as monthly totals for share calculations, provided partition fields match the business granularity.

top_n and bottom_n retain N field1 values under specified grouping and ordering, replacing unretained values with a constant1 of the same type rather than deleting other rows. The first argument of custom_filters(expression, [{field1}, {field2}], condition) must be a base aggregation. A null second argument preserves context filters, [] removes all context filters, and a field array removes filters using those fields; the third argument then adds conditions. It therefore does more than ordinary conditional aggregation.

5 Aggregate functions: conditions, ordering, nulls, and precision have specific scopes

Conditional aggregation follows the aggregate expression: sum({sales_amount}) FILTER (WHERE {order_status} = 'completed'). It constrains only that measure’s input; page filters still affect the measure unless context functions further adjust it. WITHIN GROUP (ORDER BY ...) supports only first, last, mode, percentile, and string_agg, and must not be generalized to all aggregates. Unordered first and last values are not fixed; on Oracle, string_agg requires WITHIN GROUP.

Null behavior also varies: count({field}) excludes nulls, and regr_count(ARG1, ARG2) counts only rows where both inputs are non-null. When comparison values tie, max_by and min_by randomly choose one. Ratio definitions should specify whether a zero denominator returns null, zero, or no display.

Deduplication differs by data source. On Impala, distinct_count is approximate. For exact distinct counts, use exact_distinct_count; the manual currently lists it only for Impala, and it can be used only as an aggregate function, not as an ordinary field formula or subquery field. Do not extend this distinction to other engines.

6 Advanced chart calculations and metric reuse: the support matrix has boundaries

Advanced chart calculations cover percentage, percentile, year-over-year and period-over-period comparison, accumulation, moving calculations, repeat, retention, activity, and window calculations. The matrix covers these nine chart capabilities; it is not a per-function compatibility guarantee for all HQL functions. The manual lists Engine, PostgreSQL, Oracle, MySQL 8, SQL Server 2017-CU8, Redshift, Impala, Presto, Greenplum, Hive, Spark SQL, and DB2 as supporting them. MaxCompute does not support percentile; TiDB, MySQL 5, and MongoDB do not support advanced chart calculations. Engine refers to datasets with the engine enabled.

Year-over-year and period-over-period comparisons, accumulation, moving calculations, activity, and retention require a date field in the dataset. Removing the time dimension or choosing an aggregation method outside day, week, month, quarter, or year cancels the relevant advanced calculations. Weeks follow ISO 8601, and added dimensions act as filtering conditions that affect calculations. Check date dimensions, subtotals, default filters, denominators, and null rules when reusing metrics.

Public release notes show that 6.3.2 and 6.2.5 were both released on September 23, 2026. Version 6.2 is this article’s manual baseline for function semantics, and must not be broadly described as the latest current version. When upgrading or migrating across versions, regression-check key chart results, formatting, and filter interactions.

6.1 Add distribution, correlation, and regression aggregates for diagnostics

Advanced analytics extends beyond time series. Aggregate functions include avg, median, percentile, mode, standard deviation, and variance, allowing teams to inspect distribution and variation beyond the mean. The manual defines percentile parameter P as a decimal between 0 and 1. For example, when using percentile({response_seconds}, 0.5) to describe the median position, the metric name should still identify the population, filter scope, and time granularity. mode() WITHIN GROUP (ORDER BY ...) obtains the most frequent value under specified ordering, returning the first value when frequencies tie, so the ordering field must also match the business interpretation.

corr(ARG1, ARG2) calculates the correlation coefficient between two numeric columns; covar_pop and covar_samp calculate population and sample covariance respectively. Regression functions provide further fit information: the fitted relationship for regr_slope(ARG1, ARG2) and regr_intercept(ARG1, ARG2) follows (ARG2, ARG1), regr_r2 supplies the squared correlation coefficient, and regr_count supplies the sample count with both inputs non-null. When evaluating the linear relationship between spend and conversion value with regr_slope({conversion_amount}, {ad_cost}), first verify argument order, outlier handling, and sample size. Correlation or regression alone does not establish causation.

These functions add explanatory dimensions to operational quality, price variation, user behavior, and metric monitoring. First use count and regr_count to check valid samples, then examine quantiles, means, and dispersion, and finally interpret correlation or regression parameters. Encapsulating statistical definitions together with dimensions, filters, and time ranges in metrics prevents identically named metrics from meaning different things across charts.

6.2 Preserve validated expressions as business metrics

New metrics suit confirmed advanced expressions, while charts select dimensions, display methods, and interactive filters. A reusable definition should include at least field sources and dataset IDs, base aggregation, time granularity, grouping or partitioning, default filtering rules, null and division-by-zero handling, and display format. Saving these explanations and formulas in the same metric definition reduces semantic drift caused by copying expressions across charts.

7 Validate definitions and performance with real results

7.1 Acceptance should check numbers, interactions, and data sources together

Start with a small, manually calculable sample covering window boundaries, duplicate events, missing dates, nulls, tied ordering values, and zero denominators. Compare metric outputs with manual results on the target data source, then change date granularity, add dimensions, display subtotals, and switch filters to check conformity with function definitions. Finally, record response time, returned row counts, and sample results under representative data volumes and concurrency. This process reveals differences caused by formulas, data types, time zones, data-source capabilities, and chart interactions.

• For retention, accumulation, and year-over-year comparisons, record object fields, event definitions, date granularity, endpoints, and denominators.
• Specify grouping for calculatex; validate matching-key uniqueness for lookupvalue; validate partitioning and denominator granularity for rollup_value.
• Document FILTER, WITHIN GROUP, null, and division-by-zero rules; confirm approximate versus exact deduplication on Impala.
• Test response time and sample results with representative time ranges, data volumes, concurrency, grouping cardinality, and connections; record the environment, query duration, and anomalous results. Use this evidence to decide whether function combinations and data-source configuration meet real business needs.

Advanced functions compress complex analytics into shorter expressions. Trust still comes from reviewable business definitions, clear data-source scope, and validation against real data.