Multi-stage calculations

[igorlukanin]

Cube provides rich data modeling capabilities and supports various use cases.

We would like to level up Cube's data modeling with the post-aggregation engine that would allow for further manipulations with already aggregated data, supporting more sophisticated analytics use cases or providing a way to express them in the data model in a streamlined way:

• Period-to-date calculations, such as year-to-date (YTD), quarter-to-date (QTD), or month-to-date (MTD) analyses.
• Differences or changes: finding the difference between two aggregated measures, like year-over-year sales growth.
• Fixed vs. relative comparison, which are useful when you need to compare individual items to a broader dataset.
• Ratio and percent of total calculations, calculations of ratios or percent totals that need specific control over the numerator and the denominator.
• Segmentation and grouping, calculations of advanced segmentation or grouping that is independent of the view’s granularity.

It's currently planned for October 2024.

Out of scope:

• Pre-aggregation support in multi-stage calculations #8487

[HMVarshney]

Hi.
This would be a very useful feature for me. Any updated timeline of its release?

[AlexisBocuze]

That looks like a great feature!

Testing it, I have what I think is an unexpected behaviour when I select weekly granularity.
On a measure summing revenues, the result for revenue_prior_year on the 1st week of 2025 does not match the result for revenue for the 1st week of 2024.

I can get the expected behaviour if I set

- name: revenue_last_year_weekly
  sql: revenue
  type: number
  rolling_window:
      trailing: 52 week
      leading: -51 week
      offset: start 

date	revenue	revenue_last_year_weekly
2024-01-01 W1	409,001	NA
...	...	...
2024-12-30 W1	230,002	409,001

But with the recommended implementation of multi-stage calculations, the numbers don't match anymore.

- name: revenue_prior_year
  multi_stage: true
  sql: "{revenue}"
  type: number
  time_shift:
     - time_dimension: date
        interval: 1 year
        type: prior

date	revenue	revenue_prior_year
2024-01-01 W1	409,001	NA
...	...	...
2024-12-30 W1	230,002	558,208

That approach returns matching results when the time dimension is set at yearly or monthly level, but not for weekly granularity.
I could keep the first one but it'd mean to have a dedicated measure for each time dimension granularity which is not ideal.
By the way, the first approach stops working when setting cubejs_tesseract_sql_planner = true. It return the following error:

SQL compilation error: syntax error line 19 at position 6 unexpected 'VALUES'.

Am I misinterpreting the expected behaviour? Is there a way we can align comparison vs last year on weekly granularity too?