Hi team, I have a simple metric aggregation issue that I need help with. I have a metric called 'Product Gross Order Revenue Score' in GoodData with the definition below:

SELECT 
 CASE
  WHEN SUM({fact/gross_revenue_percentile}) <= 0.2 THEN 60
  WHEN SUM({fact/gross_revenue_percentile}) <= 0.4 THEN 48
  WHEN SUM({fact/gross_revenue_percentile}) <= 0.6 THEN 36
  WHEN SUM({fact/gross_revenue_percentile}) <= 0.8 THEN 24
  WHEN SUM({fact/gross_revenue_percentile}) <= 1 THEN 12
  ELSE 8 
 END

It's supposed to return a score based on the product's gross_revenue_percentile value. I created a simple table to see the score of an individual product. A screenshot of the table is attached below. The problem is that even though I put a product id filter to see a single product, it returns all the products in the database. Why does it return all the products ignoring the product id filter? I tried to use BY clause in the metric definition but it didn't help. Thanks in advance!

Hi Emir, off the top of my head I can’t be sure what is causing this issue. Could you please send a direct link to your report so I can review this for you? 🙂

I suggest to tell in metric definition that the SUM shall be calculated for each product separately and what to do if the product is not filtered (e.g. display maximum). E.g.

SELECT MAX(
SELECT
 CASE
  WHEN SUM({fact/gross_revenue_percentile}) <= 0.2 THEN 60
  WHEN SUM({fact/gross_revenue_percentile}) <= 0.4 THEN 48
  WHEN SUM({fact/gross_revenue_percentile}) <= 0.6 THEN 36
  WHEN SUM({fact/gross_revenue_percentile}) <= 0.8 THEN 24
  WHEN SUM({fact/gross_revenue_percentile}) <= 1 THEN 12
  ELSE 8 
 END
 BY {label/product_id}
)

Hey Jakub, thanks for the help, it's really useful!