cube-js / cube-js/cube

Support more options for rollingWindow leading/trailing values

Open
#209 17 comments 0 reactions 0 assignees View on GitHub
data modeling
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

At the moment, `rollingWindow` `leading` and `trailing` parameters supports values in the following format: `(-?\d+) (minute|hour|day|week|month|year)` which can be used to define the window size to the right and left of the `offset`.

While this works OK in many scenarios, there are circumstances in which the correct value for `leading`/`trailing` parameter cannot be determined at schema creation.

For example, let's say you want to calculate the total rolling revenue since the beginning of the last month to today. To achieve this, you will need to know the query's date range (which is only available at query creation) so that you can precisely calculate the number of days to set to the `trailing` parameter.

```
{
offset: 'start',
trailing: '? day' // how many days to the start of the previous month?
}
```

To overcome such limitations, additional options can be added to enable specifying beginning/ending of a period, e.g start/end of month, year, week, n months ago etc.

One way would be to add additional options such as:

```
{
offset: 'start',
trailing: 'start of 1 month ago'
}
```

Or something similar could be attained by introducing a modifier after a `pipe` symbol, to specify the start/end of a period as follow

```
{
offset: 'start',
trailing: '1 month | start of month'
}
```

The period part can optionally be omitted:

```
{
offset: 'start',
leading: '| end of year' // calculate running total until the period's year-end
}
```

My suggestions might not be the best way to implement this feature. But I do hope they serve well to explain how this improvement can be implemented.

I am open to your ideas and suggestions.

Thanks

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.