When do we use filter, where and switch statements?
We know that we can perform aggregations and multi-dimensional analysis with Atoti. Aggregation is not always as simple as 1 + 2. Sometimes we end up in an “if…else” situation, and that is where conditional statements come in. Let’s explore some examples with the XVA use case from the Atoti CE Notebook Gallery.
Some definitions before moving on:
Measures – we refer to metrics or quantifiable data that measure certain aspects of our goals. In the code snippets below, they are represented by measure[<name of metrics>]
.
Members of a level – we refer to facts or details under the same category that helps to describe the business metrics. These members belong to the same data column, and when loaded into the cube, they become members of a level. In the code snippets below, they are represented by level[<name of level>]
.

Example 1: Filtering by member values of a given level
Let’s think along the line: I want a certain metric to be returned only for certain members of a certain category.
In this case, we want to compute the Notional value for matured trades. Hence, we return the measure NotionalUSD
only when the members of the level MaturityDate
is on or before the current date.
from datetime import date
current_date = date.today()
measure["Matured Notional Value"] = tt.filter(
measure["NotionalUSD"], level["MaturityDate"] <= current_date
)

We use the atoti.filter
function in instances where it acts as a slicer across the level. It returns the values of a measure for either a given member of a level or a combination of selection criteria on multiple levels. Therefore, it does not check for None type values.
Tip: We can apply a conjunction of conditions using the &
operator for and
, and |
operator for or
. Each condition is surrounded by parenthesis:
(level["Desk"].isin("Commodities Desk", "Equity Desk", "Rates Desk"))
& (level["CounterpartyId"] == "Banco Popular - Societa Cooperativa")
Note: Do not confuse atoti.filter
with the filter
parameter of the query_cube.query
:
cp_mtm_df = cube.query(
measure["MtM_vector.SUM"],
measure["contributors.COUNT"],
levels=[level["Desk"], level["CounterpartyId"]],
filter=(level["Desk"].isin("Commodities Desk", "Equity Desk", "Rates Desk"))
& (level["CounterpartyId"] == "Banco Santander SA",),
)
Using the filter
parameter in a query to the cube, we can apply conditional filtering on the returned dataset to obtain only the relevant results for the selected members. In the above snippet, we return the MTM (mark to market) vector for the counterparty with ID “Banco Popular – Societa Cooperativa” whose desks fall under “Commodities Desk”, “Equity Desk” or “Rates Desk”.

To summarize,
atoti.filter
returns values of a measure conditionally, while thefilter
parameter returns a subset of the results from a query conditionally.
Example 2: Comparing level against level – atoti.where
vs atoti.filter
Let’s think along the line: I want a certain business metric to be returned based on the comparison of members between two different levels.
Assume we have two types of dates: TimePointDate (scenario date) and BreakDate (Trade break date). What should we do if we want the MTM vector to be returned when TimePointDate is before or the same as BreakDate?
We cannot use atoti.filter
in this case as we are comparing members of two levels in this example. atoti.filter
only compares levels against constant values. atoti.where
to the rescue!
measure["Filtered MtM_vector.SUM"] = tt.where(
level["TimePointDate"] <= level["BreakDate"], measure["MtM_vector.SUM"]
)

The above is a classic example of how we use atoti.where
function:
atoti.where(condition, true_value, false_value=None)
where true_value can be a measure, measure definition, constant value or another atoti.where
condition that is returned when the condition is true.
Similarly, false_value is the value returned when the condition is false. It defaulted to None if nothing is specified.
Excluding values when level value is not available
While we cannot filter against None type values, we can do so with atoti.where
:
measure["CE"] = tt.where(~level["TimePoint"].isnull(), measure["CE_vector"], None)
This is very handy when the grand-total value across different members is not necessary or doesn’t make sense:

Example 3: Assigning values conditionally to level members – atoti.where
vs atoti.switch
Let’s think along the line: I need some members within the same levels to have a dedicated metric while the rest populate a generic metric.
Below shows the members of the level TypeofRiskMitigant
and their corresponding measures:
- No Risk Mitigant: Average of credit exposure (CE.MEAN)
- Trade Break Date: Average of credit exposure where TimePointDate is before or on the BreakDate (CEBreakDate.MEAN)
There are two methods we can use to achieve this in Atoti. In the first method, we have embedded atoti.where
conditions for each member that requires special handling:
measure["EE (where)"] = tt.where(
level["TypeofRiskMitigant"] == "No Risk Mitigant",
measure["CE.MEAN"],
tt.where(
level["TypeofRiskMitigant"] == "Trade Break Date",
measure["CEBreakDate.MEAN"],
),
)
The second method uses the atoti.switch
function, is more elegant and readable:
measure["EE (switch)"] = tt.switch(
level["TypeofRiskMitigant"],
{
"No Risk Mitigant": measure["CE.MEAN"],
"Trade Break Date": measure["CEBreakDate.MEAN"],
},
)
With respect to the subject level[“TypeofRiskMitigant”]
, each member of the level is expressed as a key to its corresponding metric as a case in a dictionary. We can also define a default value if necessary.
Example 4: When atoti.where
behaves like an atoti.switch
Let’s think along the line: I require different computations for different ranges of values of a measure.
We will take the example where we impose limit monitoring on the Potential Future Exposure (PFE). We display different symbols when the PFE value crosses different threshold values.
measure["Breaches Status"] = tt.where(
{
measure["PFE 1Y"] > measure["PFE Limit"]: "🟥",
measure["PFE 1Y"] == measure["PFE Limit"]: "🔶",
measure["PFE 1Y"] < measure["PFE Limit"]: "🟢",
}
)
The atoti.where
syntax, in this case, looks similar to the syntax of atoti.switch
. Each condition is a key to its corresponding value in the dictionary. The value can be a measure or a constant.

Summing it up
We saw how we can handle aggregations dynamically using conditional statements in Atoti. These examples are available in the corresponding notebook in the Atoti CE notebook gallery.
For a quick view, the table below summarizes it all:

We don’t just stop here when it comes to dynamic aggregation. atoti.scope
is another way where we can handle computations differently at different levels, resulting in what we call non-linear aggregation. Check out Atoti videos on scope:
Curious about Atoti? Try it yourself using the free Atoti Community Edition.