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 the filter 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.