Pre-aggregations don't respect `offset: start` in rolling window measures
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Describe the bug**
Cube returns different results when a rolling window measure with `offset` set to `start` is served directly from the data source vs. from a pre-aggregation. Looks like `offset` is ignored by pre-aggregations.
**To Reproduce**
Steps to reproduce the behavior:
1. Use the following data model:
```javascript
cube(`order_rolling`, {
sql: `
SELECT 10 AS value, '2023-11-01'::TIMESTAMP AS date UNION ALL
SELECT 10 AS value, '2023-12-01'::TIMESTAMP AS date UNION ALL
SELECT 10 AS value, '2024-01-01'::TIMESTAMP AS date UNION ALL
SELECT 30 AS value, '2024-02-01'::TIMESTAMP AS date UNION ALL
SELECT 50 AS value, '2024-03-01'::TIMESTAMP AS date UNION ALL
SELECT 80 AS value, '2024-04-01'::TIMESTAMP AS date
`,
dimensions: {
date: {
sql: `date`,
type: `time`
},
},
measures: {
current_month_sum: {
sql: `value`,
type: `sum`,
rolling_window: {
trailing: `3 month`,
offset: `start`
}
},
},
// preAggregations: {
// main: {
// measures: [order_rolling.current_month_sum],
// timeDimension: order_rolling.date,
// granularity: `day`,
// refresh_key: {
// every: `1 week`,
// },
// }
// }
});
```
2. Run the following query:
```json
{
"limit": 5000,
"timeDimensions": [
{
"dimension": "order_rolling.date",
"granularity": "month",
"dateRange": [
"2023-11-01",
"2024-07-01"
]
}
],
"measures": [
"order_rolling.current_month_sum"
]
}
```
3. Observe the result
4. Uncomment the pre-aggregation
5. Run the same query
6. Compare the result with the initial one, they will be different.
**Expected behavior**
With a pre-aggregation or not, the result should be the same.
**Screenshots**
No pre-aggregation:
Pre-aggregation:
**Version:**
0.35.69
**Additional context**
Reported in Slack: https://cube-js.slack.com/archives/C04KDTE2EF7/p1722975927331859
Contributor guide
Assessment
This issue has not been assessed yet.