We already know how to create filters on metrics to restrict data returned by a query using a
HAVING clause (Chapter 4, Objects – Facts and Metrics). In this recipe, we will go a step further; we want to filter on a metric value calculated at a different level than the report. Say that you want to know which products sold more than 1,000 units on a yearly basis all over the world, and you also want to see the countries where the products were sold. You have a report with
Country, so the
Sum OrderQuantity from FactResellerSales metric is aggregated at year, product, and country level; but we need it to be aggregated only at year and product level for the filter to work. Here comes the qualification ...