atoti.function.parent_value module

atoti.parent_value(measure, *, degrees, apply_filters=False, total_value=None)

Return a measure which values are equal to the values of the given measure, at a member that is located at a higher level on each of the specified hierarchy. This operation is also called drilling up a hierarchy.

Parameters

Example

>>> df = pd.DataFrame(
...     columns=["Year", "Month", "Day", "Shop", "Quantity", "Other"],
...     data=[
...         (2019, 7, 1, "Shop1", 15, 245),
...         (2019, 7, 2, "Shop1", 20, 505),
...         (2019, 6, 1, "Shop2", 25, 115),
...         (2019, 6, 2, "Shop2", 15, 135),
...         (2018, 7, 1, "Shop1", 5, 55),
...         (2018, 7, 2, "Shop2", 10, 145),
...         (2018, 6, 1, "Shop1", 15, 145),
...         (2018, 6, 2, "Shop2", 5, 155),
...     ],
... )
>>> table = session.read_pandas(
...     df,
...     table_name="Parent Value",
... )
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> h["Date"] = [table["Year"], table["Month"], table["Day"]]
>>> m["Degree 1"] = tt.parent_value(m["Quantity.SUM"], degrees={h["Date"]: 1})
>>> m["Degree 2"] = tt.parent_value(m["Quantity.SUM"], degrees={h["Date"]: 2})
>>> m["Degree 2 with Quantity total"] = tt.parent_value(
...     m["Quantity.SUM"],
...     degrees={h["Date"]: 2},
...     total_value=m["Quantity.SUM"],
... )
>>> m["Degree 2 with Other total"] = tt.parent_value(
...     m["Quantity.SUM"],
...     degrees={h["Date"]: 2},
...     total_value=m["Other.SUM"],
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Other.SUM"],
...     m["Degree 1"],
...     m["Degree 2"],
...     m["Degree 2 with Quantity total"],
...     m["Degree 2 with Other total"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
                Quantity.SUM Other.SUM Degree 1 Degree 2 Degree 2 with Quantity total Degree 2 with Other total
Year  Month Day
Total                    110     1,500                                            110                     1,500
2018                      35       500      110                                   110                     1,500
      6                   20       300       35      110                          110                       110
            1             15       145       20       35                           35                        35
            2              5       155       20       35                           35                        35
      7                   15       200       35      110                          110                       110
            1              5        55       15       35                           35                        35
            2             10       145       15       35                           35                        35
2019                      75     1,000      110                                   110                     1,500
      6                   40       250       75      110                          110                       110
            1             25       115       40       75                           75                        75
            2             15       135       40       75                           75                        75
      7                   35       750       75      110                          110                       110
            1             15       245       35       75                           75                        75
            2             20       505       35       75                           75                        75
>>> h["Date"].slicing = True
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Other.SUM"],
...     m["Degree 1"],
...     m["Degree 2"],
...     m["Degree 2 with Quantity total"],
...     m["Degree 2 with Other total"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
               Quantity.SUM Other.SUM Degree 1 Degree 2 Degree 2 with Quantity total Degree 2 with Other total
Year Month Day
2018 6     1             15       145       20       35                           35                        35
           2              5       155       20       35                           35                        35
     7     1              5        55       15       35                           35                        35
           2             10       145       15       35                           35                        35
2019 6     1             25       115       40       75                           75                        75
           2             15       135       40       75                           75                        75
     7     1             15       245       35       75                           75                        75
           2             20       505       35       75                           75                        75
>>> h["Date"].slicing = False
>>> m["Degree 1 with applied filter"] = tt.parent_value(
...     m["Quantity.SUM"], degrees={h["Date"]: 1}, apply_filters=True
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Degree 1"],
...     m["Degree 1 with applied filter"],
...     levels=[l["Day"]],
...     include_totals=True,
...     condition=l["Year"] == "2018",
... )
                Quantity.SUM Degree 1 Degree 1 with applied filter
Year  Month Day
Total                     35
2018                      35      110                           35
      6                   20       35                           35
            1             15       20                           20
            2              5       20                           20
      7                   15       35                           35
            1              5       15                           15
            2             10       15                           15
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Degree 1"],
...     m["Degree 1 with applied filter"],
...     levels=[l["Day"]],
...     include_totals=True,
...     condition=l["Shop"] == "Shop1",
... )
                Quantity.SUM Degree 1 Degree 1 with applied filter
Year  Month Day
Total                     55
2018                      20       55                           55
      6                   15       20                           20
            1             15       15                           15
      7                    5       20                           20
            1              5        5                            5
2019                      35       55                           55
      7                   35       35                           35
            1             15       35                           35
            2             20       35                           35

See also

atoti.total() to take the value at the top level member on each given hierarchy.

Return type

MeasureDescription