atoti.shift()#

atoti.shift(measure, on, /, *, offset=1, partitioning=None, mode='measure')#

Return a measure equal to the passed measure shifted to another member of the hierarchy.

Parameters:

measure (VariableMeasureConvertible) – The measure to shift.
on (Hierarchy) – The hierarchy to shift on.

offset (int) –

The number of members to shift by.

>>> df = pd.DataFrame(
...     columns=["Country", "City", "Price"],
...     data=[
...         ("France", "Lille", 1),
...         ("France", "Marseille", None),
...         ("France", "Nantes", 3),
...         ("France", "Paris", 4),
...         ("Japan", "Sapporo", 5),
...         ("Japan", "Tokyo", 6),
...         ("Japan", "Yokohama", None),
...     ],
... )
>>> table = session.read_pandas(
...     df, keys={"Country", "City"}, table_name="Country and city"
... )
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> m["Shifted by -1"] = tt.shift(m["Price.SUM"], h["City"], offset=-1)
>>> m["Shifted by 2"] = tt.shift(m["Price.SUM"], h["City"], offset=2)
>>> cube.query(
...     m["Price.SUM"],
...     m["Shifted by -1"],
...     m["Shifted by 2"],
...     levels=[l["City"]],
...     include_totals=True,
... )
          Price.SUM Shifted by -1 Shifted by 2
City
Total         19.00
Lille          1.00                       3.00
Marseille                    1.00         4.00
Nantes         3.00                       5.00
Paris          4.00          3.00         6.00
Sapporo        5.00          4.00
Tokyo          6.00          5.00
Yokohama                     6.00
>>> del m["Shifted by -1"]
>>> del m["Shifted by 2"]

partitioning (Level | None) –

The level in the hierarchy at which to start over again.

>>> h["Geography"] = [l["Country"], l["City"]]
>>> for name in h["Geography"]:
...     del h[name]
>>> m["No partitioning"] = tt.shift(m["Price.SUM"], h["Geography"])
>>> m["Partitioned by Country"] = tt.shift(
...     m["Price.SUM"],
...     h["Geography"],
...     partitioning=l["Country"],
... )
>>> cube.query(
...     m["Price.SUM"],
...     m["No partitioning"],
...     m["Partitioned by Country"],
...     levels=[l["City"]],
...     include_totals=True,
... )
                  Price.SUM No partitioning Partitioned by Country
Country City
Total                 19.00
France                 8.00           11.00
        Lille          1.00
        Marseille                      3.00                   3.00
        Nantes         3.00            4.00                   4.00
        Paris          4.00            5.00
Japan                 11.00
        Sapporo        5.00            6.00                   6.00
        Tokyo          6.00

mode (Literal['measure', 'hierarchy']) –

Whether the shift is driven by the values of the input measure in the query result or by the members of the on hierarchy.

>>> df = pd.DataFrame(
...     columns=["Desk", "Day", "Value"],
...     data=[
...         ("A", "Mon", 1),
...         ("A", "Tue", 2),
...         ("A", "Wed", 3),
...         ("B", "Mon", 10),
...         ("B", "Tue", 20),
...         ("C", "Mon", 100),
...         ("C", "Wed", 300),
...     ],
... )
>>> table = session.read_pandas(
...     df, keys={"Desk", "Day"}, table_name="Desk and day"
... )
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> m["mode=measure"] = tt.shift(
...     m["Value.SUM"], h["Day"], offset=-1, mode="measure"
... )
>>> m["mode=hierarchy"] = tt.shift(
...     m["Value.SUM"], h["Day"], offset=-1, mode="hierarchy"
... )
>>> cube.query(
...     m["Value.SUM"],
...     m["mode=measure"],
...     m["mode=hierarchy"],
...     levels=[l["Day"], l["Desk"]],
...     include_totals=True,
... )
           Value.SUM mode=measure mode=hierarchy
Day   Desk
Total            436
Mon              111
      A            1
      B           10
      C          100
Tue               22          111            111
      A            2            1              1
      B           20           10             10
      C                                      100
Wed              303           22             22
      A            3            2              2
      B                                       20
      C          300          100

mode=measure:
- (Tue, C) and (Wed, B) have no values because Value.SUM also has no values at these locations.
  
  → The returned measure always evaluates to None where its input is also None.
- (Wed, C) is 100 because shifting by offset -1 along Day lands on (Tue, C) at which Value.SUM has no value so the shifting continues to the first non-None value: the one at (Mon, C).
  
  → offset is lax.
mode=hierarchy:
- (Tue, C) is 100 even though Value.SUM has no value at this exact location because shifting by offset -1 along Day lands on (Mon, C) which does have a value.
  
  → The returned measure evaluates to the input measure’s value at the shifted location, even where the input measure at the current location has no value.
- (Wed, C) has no value because shifting by offset -1 along Day lands on (Tue, C) at which Value.SUM has no value.
  
  → offset is strict.

Return type:

MeasureDefinition