Using filters in custom column calculations

In this blog, we have learnt how to use custom column. But in certain cases we may want to pass multiple filter conditions and do calculation’s in custom column. For example, we want to find out sales ration wherein in numerator = total sales when sale type is domestic divided by denominator = total sales when sale type = international.

This is not a very straightforward use case. In this blog we will learn how to do the same.

1. Use Case 1 – Divide Conditional Aggregated Values

This use case creates a Custom Column that calculates the ratio below ratio

The calculation uses conditional aggregation so that only rows matching each destination contribute to the corresponding total.

Create a custom column. Read this blog to learn how to create custom column. In the custom column, put the below expression.

Custom Column expression

(
    SUM(
        CASE
            WHEN destination = 'Agra'
            THEN "travel_details"."travel_cost"
            ELSE 0
        END
    )
)
/
(
    SUM(
        NULLIF(
            CASE
                WHEN destination = 'Ambala'
                THEN "travel_details"."travel_cost"
                ELSE 0
            END,
            0
        )
    )
)

How the expression works

Example result