>>> df = pd.DataFrame(
... columns=[
... "Country",
... "City",
... "Target Country",
... "Target City",
... "Quantity",
... ],
... data=[
... ("Germany", "Berlin", "UK", "London", 15),
... ("UK", "London", "Germany", "Berlin", 24),
... ("USA", "New York", "UK", "London", 10),
... ("USA", "New York", "France", "Paris", 3),
... ("USA", "Seattle", "Germany", "Berlin", 3),
... ],
... )
>>> table = session.read_pandas(df, table_name="At")
>>> cube = session.create_cube(table, mode="manual")
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> h["Geography"] = [table["Country"], table["City"]]
>>> h["Target Geography"] = [
... table["Target Country"],
... table["Target City"],
... ]
>>> m["Quantity.SUM"] = tt.agg.sum(table["Quantity"])
>>> # Using a constant matching an existing member of the level:
>>> m["USA quantity"] = tt.at(m["Quantity.SUM"], l["Country"] == "USA")
>>> cube.query(
... m["Quantity.SUM"],
... m["USA quantity"],
... levels=[l["Country"]],
... )
Quantity.SUM USA quantity
Country
Germany 15 16
UK 24 16
USA 16 16
>>> # Using another level whose current member the level on the left of the condition will be shifted to:
>>> m["Target quantity"] = tt.at(
... m["Quantity.SUM"],
... (l["Country"] == l["Target Country"]) & (l["City"] == l["Target City"]),
... )
>>> cube.query(
... m["Quantity.SUM"],
... m["Target quantity"],
... levels=[l["City"], l["Target City"]],
... )
Quantity.SUM Target quantity
Country City Target Country Target City
Germany Berlin UK London 15 24
UK London Germany Berlin 24 15
USA New York France Paris 3
UK London 10 24
Seattle Germany Berlin 3 15
