atoti.CumulativeScope#

final class atoti.CumulativeScope#

Scope performing a cumulative aggregation.

Example

Using this scope with atoti.agg.sum() to perform a cumulative sum (also called running sum or prefix sum):

>>> from datetime import date
>>> df = pd.DataFrame(
...     columns=["Date", "Quantity"],
...     data=[
...         (date(2019, 7, 1), 15),
...         (date(2019, 7, 2), 20),
...         (date(2019, 6, 1), 25),
...         (date(2019, 6, 2), 15),
...         (date(2018, 7, 1), 5),
...         (date(2018, 7, 2), 10),
...         (date(2018, 6, 1), 15),
...         (date(2018, 6, 2), 5),
...     ],
... )
>>> table = session.read_pandas(df, table_name="Cumulative")
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> cube.create_date_hierarchy("Date", column=table["Date"])
>>> h["Date"] = {**h["Date"], "Date": table["Date"]}
>>> m["Quantity.SUM"] = tt.agg.sum(table["Quantity"])
>>> m["Cumulative quantity"] = tt.agg.sum(
...     m["Quantity.SUM"], scope=tt.CumulativeScope(l["Day"])
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Cumulative quantity"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
                Quantity.SUM Cumulative quantity
Year  Month Day
Total                    110                 110
2018                      35                  35
      6                   20                  20
            1             15                  15
            2              5                  20
      7                   15                  35
            1              5                  25
            2             10                  35
2019                      75                 110
      6                   40                  75
            1             25                  60
            2             15                  75
      7                   35                 110
            1             15                  90
            2             20                 110
dense: bool = False#

When True, all members of level, even those with no value for the underlying measure, will be taken into account, possibly leading to repeated values.

>>> m["Quantity L"] = tt.where(m["Quantity.SUM"] > 10, m["Quantity.SUM"])
>>> m["Cumulative quantity L"] = tt.agg.sum(
...     m["Quantity L"], scope=tt.CumulativeScope(l["Day"])
... )
>>> m["Dense cumulative quantity L"] = tt.agg.sum(
...     m["Quantity L"], scope=tt.CumulativeScope(l["Day"], dense=True)
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Quantity L"],
...     m["Cumulative quantity L"],
...     m["Dense cumulative quantity L"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
                Quantity.SUM Quantity L Cumulative quantity L Dense cumulative quantity L
Year  Month Day
Total                    110        110                    90                          90
2018                      35         35                    15                          15
      6                   20         20                    15                          15
            1             15         15                    15                          15
            2              5                                                           15
      7                   15         15                                                15
            1              5                                                           15
            2             10                                                           15
2019                      75         75                    90                          90
      6                   40         40                    55                          55
            1             25         25                    40                          40
            2             15         15                    55                          55
      7                   35         35                    90                          90
            1             15         15                    70                          70
            2             20         20                    90                          90
level: HasIdentifier[LevelIdentifier] | LevelIdentifier#

The level along which member values are cumulated.

partitioning: HasIdentifier[LevelIdentifier] | LevelIdentifier | None = None#

The level at which to start the aggregation over.

If not None, partitioning must part of the same hierarchy as level and be “above” it (i.e. before it in list(hierarchy)).

>>> m["Partitioned by month"] = tt.agg.sum(
...     m["Quantity.SUM"],
...     scope=tt.CumulativeScope(l["Day"], partitioning=l["Month"]),
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Partitioned by month"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
                Quantity.SUM Partitioned by month
Year  Month Day
Total                    110
2018                      35
      6                   20                   20
            1             15                   15
            2              5                   20
      7                   15                   15
            1              5                    5
            2             10                   15
2019                      75
      6                   40                   40
            1             25                   25
            2             15                   40
      7                   35                   35
            1             15                   15
            2             20                   35
window: range | tuple[str, str] | tuple[str | None, str] | tuple[str, str | None] | None = None#

The window defining the sliding range selecting members before and after the current one (using level’s order) to be aggregated.

The window can be a:

  • range starting with a <=0 value and ending with a >=0 value.

    >>> m["3 previous members window"] = tt.agg.sum(
    ...     m["Quantity.SUM"],
    ...     scope=tt.CumulativeScope(l["Day"], window=range(-3, 0)),
    ... )
    >>> cube.query(
    ...     m["Quantity.SUM"],
    ...     m["3 previous members window"],
    ...     levels=[l["Day"]],
    ...     include_totals=True,
    ... )
                    Quantity.SUM 3 previous members window
    Year  Month Day
    Total                    110                        75
    2018                      35                        35
          6                   20                        20
                1             15                        15
                2              5                        20
          7                   15                        35
                1              5                        25
                2             10                        35
    2019                      75                        75
          6                   40                        55
                1             25                        45
                2             15                        55
          7                   35                        75
                1             15                        65
                2             20                        75
    
  • time period window as a two-element tuple of either None or a period as specified by Java’s Period.parse().

    >>> m["2 days window"] = tt.agg.sum(
    ...     m["Quantity.SUM"],
    ...     scope=tt.CumulativeScope(l["Date"], window=("-P2D", None)),
    ... )
    >>> m["2 days window partitioned by month"] = tt.agg.sum(
    ...     m["Quantity.SUM"],
    ...     scope=tt.CumulativeScope(
    ...         level=l["Date"],
    ...         window=("-P2D", None),
    ...         partitioning=l["Month"],
    ...     ),
    ... )
    >>> cube.query(
    ...     m["Quantity.SUM"],
    ...     m["2 days window"],
    ...     m["2 days window partitioned by month"],
    ...     levels=[l["Day"]],
    ...     include_totals=True,
    ... )
                    Quantity.SUM 2 days window 2 days window partitioned by month
    Year  Month Day
    Total                    110            35
    2018                      35            15
          6                   20            20                                 20
                1             15            15                                 15
                2              5            20                                 20
          7                   15            15                                 15
                1              5             5                                  5
                2             10            15                                 15
    2019                      75            35
          6                   40            40                                 40
                1             25            25                                 25
                2             15            40                                 40
          7                   35            35                                 35
                1             15            15                                 15
                2             20            35                                 35
    

Default to range(-∞, 0), meaning that the aggregated value for a given member is computed using all the members before it, itself, and no members after it.