# Understanding conditional statements in Atoti

When do we use filter, where and switch statements? We know that we can perform aggregations and…

Hui Fang Yeo
February 27, 2023

# Understanding conditional statements in Atoti

## 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(
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"],
},
)
``````

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.

## Like this post? Please share

### What is DirectQuery?

Combine Atoti and Data Warehouses to enjoy the deep-diving capability of Atoti on top of your existing...

### Querying with Atoti

In Atoti, we integrate data from different sources into an in-memory data cube. Without worrying about the...

### Measuring Interest Rate Risk in the Banking Book (IRRBB) with Atoti

Manage and analyze large amounts of transactions and historical data Interest Rate Risk in the Banking Book...
Documentation
Information