Prevent reports with metric level TOP() filters from breaking when adding another metric

tomas.ferko@keboola.com

Use case & howto:

We want to create a report that functions as a drill in and shows only the top 20 customers that share an attribute according to the revenue their orders brought in. However we want to show provisions (or something else) for the top 20 orders as well. To ensure the best functioning of the report as an agile drill in, it's best to put the TOP 20 filter aggregation right in the metric like this. Let's call it:

Sum Revenue by TOP20 Customers

SELECT SUM(Amount) by (ID Order)

WHERE TOP(20) IN

(SELECT SUM(Amount)) BY (Customer ID)

This will ensure that we always the SUM for the TOP 20 customers in our report no matter what. Being a simple table report it should probably look a something like this.

Now we want to add another metric to the report. Let's say “Provisions”. We'll do it as a simple sum first. We see that as it is not filtered as the previous TOP20 metric it breaks the report by showing the sum of all items all of a sudden:

What we need to do in order for this to function properly is to lock the “provisions” metric to the “TOP 20” metric in order to make the report show only the provisions for the TOP 20 customers by revenue like this:

SELECT SUM (Provisions)

WHERE Sum Revenue by TOP20 Customers > 0