atoti.function.rank module

atoti.rank(measure, hierarchy, *, ascending=True, apply_filters=True)

Return a measure equal to the rank of a hierarchy’s members according to a reference measure.

Members with equal values are further ranked using the level order.

Parameters

• measure (MeasureDescription) – The measure on which the ranking is done.

• hierarchy (Hierarchy) – The hierarchy containing the members to rank.

• ascending (bool) – When set to False, the 1st place goes to the member with greatest value.

• apply_filters (bool) – When True, query filters will be applied before ranking members. When False, query filters will be applied after the ranking, resulting in “holes” in the ranks.

Example

>>> df = pd.DataFrame(
...     columns=["Year", "Month", "Day", "Quantity"],
...     data=[
...         (2000, 1, 1, 15),
...         (2000, 1, 2, 10),
...         (2000, 2, 1, 30),
...         (2000, 2, 2, 20),
...         (2000, 2, 5, 30),
...         (2000, 4, 4, 5),
...         (2000, 4, 5, 10),
...         (2020, 12, 6, 15),
...         (2020, 12, 7, 15),
...     ],
... )
>>> table = session.read_pandas(
...     df,
...     table_name="Rank",
... )
>>> cube = session.create_cube(table)
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> h["Date"] = [table["Year"], table["Month"], table["Day"]]
>>> m["Rank"] = tt.rank(m["Quantity.SUM"], h["Date"])
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Rank"],
...     levels=[l["Day"]],
...     include_totals=True,
... )
                Quantity.SUM Rank
Year  Month Day
Total                    150    1
2000                     120    2
      1                   25    2
            1             15    2
            2             10    1
      2                   80    3
            1             30    2
            2             20    1
            5             30    3
      4                   15    1
            4              5    1
            5             10    2
2020                      30    1
      12                  30    1
            6             15    1
            7             15    2
>>> m["Rank with filters not applied"] = tt.rank(
...     m["Quantity.SUM"], h["Date"], apply_filters=False
... )
>>> cube.query(
...     m["Quantity.SUM"],
...     m["Rank"],
...     m["Rank with filters not applied"],
...     levels=[l["Month"]],
...     include_totals=True,
...     condition=l["Year"] == "2000",
... )
            Quantity.SUM Rank Rank with filters not applied
Year  Month
Total                120    1                             1
2000                 120    1                             2
      1               25    2                             2
      2               80    3                             3
      4               15    1                             1

2000-01-01 and 2000-01-05 have the same Quantity.SUM value so they’re ranked according to l[“Day”].order.

Return type

MeasureDescription