atoti.date_shift()

atoti.date_shift(
    measure: VariableMeasureConvertible,
    on: Hierarchy,
    /,
    *,
    offset: str,
    dense: bool = False,
    fallback: Literal[‘past’, ‘interpolated’, ‘future’] | None = None,
) → MeasureDefinition

Return a measure equal to the passed measure shifted to another date.

Parameters

measure

The measure to shift.

on

The hierarchy to shift on. Only hierarchies with their last level with a data_type of "LocalDate" or "LocalDateTime" are supported.

offset

The period to shift by as specified by Java’s Period.parse().

dense

If False, the returned measure will evaluate to None everywhere the input measure evaluates to None. If True, the returned measure will be evaluated on all the queried members of the on hierarchy, even if the input measure evaluates to None there. In any case, facts are never “created”: if measure evaluates to a non-None value on 2025-01-01 and offset="-P2D" but 2025-01-03 is not a member of the on hierarchy, 2025-01-03 will remain absent from the query results.

fallback

The value to use if measure evaluates to None at the shifted location:

None: No value.
past: Value at the previous date in chronological order.
interpolated: Linear interpolation of the values at the past and future existing dates or None if either date is missing.
future: Value at the next date in chronological order.

>>> from datetime import date
>>> df = pd.DataFrame(
...     columns=["Date", "Price"],
...     data=[
...         (date(2020, 8, 1), 5),
...         (date(2020, 8, 15), 7),
...         (date(2020, 8, 30), 15),
...         (date(2020, 8, 31), 15),
...         (date(2020, 9, 1), 10),
...         (date(2020, 9, 30), 21),
...         (date(2020, 10, 1), 9),
...         (date(2020, 10, 31), 8),
...     ],
... )
>>> table = session.read_pandas(df, keys={"Date"}, table_name="Fallback example")
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> cube.create_date_hierarchy(
...     "Date parts",
...     column=table["Date"],
...     levels={"Year": "y", "Month": "M"},
... )
>>> h["Date"] = {**h["Date parts"], "Date": table["Date"]}
>>> m["Exact (+)"] = tt.date_shift(m["Price.SUM"], h["Date"], offset="P1M")
>>> m["Exact (-)"] = tt.date_shift(m["Price.SUM"], h["Date"], offset="-P1M")
>>> m["Past"] = tt.date_shift(
...     m["Price.SUM"], h["Date"], offset="P1M", fallback="past"
... )
>>> m["Interpolated"] = tt.date_shift(
...     m["Price.SUM"], h["Date"], offset="P1M", fallback="interpolated"
... )
>>> m["Future"] = tt.date_shift(
...     m["Price.SUM"], h["Date"], offset="P1M", fallback="future"
... )
>>> cube.query(
...     m["Price.SUM"],
...     m["Exact (+)"],
...     m["Exact (-)"],
...     m["Past"],
...     m["Interpolated"],
...     m["Future"],
...     levels=[l["Date"]],
...     include_totals=True,
... )
                       Price.SUM Exact (+) Exact (-) Past Interpolated Future
Year  Month Date
Total                         90
2020                          90
      8                       42
            2020-08-01         5        10             10        10.00     10
            2020-08-15         7                       10        15.31     21
            2020-08-30        15        21             21        21.00     21
            2020-08-31        15        21             21        21.00     21
      9                       31
            2020-09-01        10         9         5    9         9.00      9
            2020-09-30        21                  15    9         8.03      8
      10                      17
            2020-10-01         9                  10    8
            2020-10-31         8                  21    8

Explanations:

Exact (+):
- The value for 2020-08-31 is taken from 2020-09-30 even though 31 != 30 because there are both the last day of their respective month.
Exact (-):
- The value for 2020-10-31 is taken from 2020-09-30 for the same reason.
Interpolated:
- 10.00, 21.00, 21.00, and 9.00: no interpolation required since there is an exact match.
- 15.31: linear interpolation of 2020-09-01’s 10 and 2020-09-30’s 21 at 2020-09-15.
- 8.03: linear interpolation of 2020-10-01’s 9 and 2020-10-31’s 8 at 2020-10-30.
- ∅: no interpolation possible because there are no records after 2020-10-31.

Behavior of the dense parameter:

>>> df = pd.DataFrame(
...     columns=["Date", "City", "Price"],
...     data=[
...         (date(2020, 8, 1), "London", 10),
...         (date(2020, 8, 1), "New York", 12),
...         (date(2020, 9, 1), "New York", 15),
...         (date(2020, 10, 1), "London", 18),
...         (date(2020, 10, 1), "New York", 20),
...     ],
... )
>>> table = session.read_pandas(
...     df, keys={"Date", "City"}, table_name="Dense example"
... )
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> cube.create_date_hierarchy(
...     "Date parts", column=table["Date"], levels={"Year": "y", "Month": "M"}
... )
>>> h["Date"] = {**h["Date parts"], "Date": table["Date"]}
>>> m["Sparse"] = tt.date_shift(
...     m["Price.SUM"], h["Date"], offset="P1M", dense=False
... )
>>> m["Dense"] = tt.date_shift(m["Price.SUM"], h["Date"], offset="P1M", dense=True)
>>> cube.query(
...     m["Price.SUM"], m["Sparse"], m["Dense"], levels=[l["Date"], l["City"]]
... )
                               Price.SUM Sparse Dense
Year Month Date       City
2020 8     2020-08-01 London          10
                      New York        12     15    15
     9     2020-09-01 London                       18
                      New York        15     20    20
     10    2020-10-01 London          18
                      New York        20

Explanations:

Sparse:
- There is no value for (2020-09-01, London) because, although both members exist separately, no fact contains both simultaneously.
Dense:
- The value for (2020-09-01, London) is taken from (2020-10-01, London).
- There are no values for 2020-10-01 because 2020-11-01 is not a member of the Date hierarchy.

See also: shift().

Releases and upgrades

Getting started

Guides

API reference

date_shift()

Parameters

measure

on

offset

dense

fallback

​Parameters

​measure

​on

​offset

​dense

​fallback

Parameters

measure

on

offset

dense

fallback