Hi everyone, I am trying to create a metric to sum the multiplication of each product's sale price with the last price of the product: • Retrieve the Last Price: ◦ Select the last price for each product based on the maximum sale date. • Calculate Total Sales: ◦ For each sale, multiply the sale quantity by the last price of the corresponding product. ◦ Sum the results to get the total sales.

WITH last_prices AS (
    SELECT product_id, price AS last_price
    FROM fact_sales
    WHERE (product_id, sale_date) IN (
        SELECT product_id, MAX(sale_date)
        FROM fact_sales
        GROUP BY product_id
    )
)

SELECT SUM(fs.quantity * lp.last_price) AS total_sales
FROM fact_sales fs
JOIN last_prices lp ON fs.product_id = lp.product_id;

The only way I found using GoodData was by adding product_id as rows in the table visualisation then sum all the rows of the table. But I wanted to get directly a single output number using a metric, otherwise I won't be able to visualize it correctly nor use it compose further metrics.

Hi Karim, it is possible to do this in a MAQL metric: (EDIT: see much simpler solution in the comment below) first, let’s define a helper metric that calculates number of minutes since a fixed date (it will help us later in a ranking metric which requires a metric, not date): _*_helper_date_minutes*_ =

SELECT DATETIME_DIFF("2020-01-01 00:00",{label/date.minute}, MINUTE) BY {dataset/fact_sales}

the BY section here tells the system to calculate the difference in the fact sales dataset (there could be theoretically more datasets connected to the same date) Now we can define the metric for last price for product: _*_last_product_price=*_

SELECT MAX(
  SELECT {fact/unit_price}
  WHERE (
    SELECT ROW_NUMBER({metric/_helper_date_minutes}) DESC WITHIN({label/product_id})
  )=1
) BY {label/product_id}, ALL OTHER

here we are using the first date-based metric to rank the sales records in descending order with ROW_NUMBER function and select the one with row_numer = 1 (meaning the latest because of the DESC) and using the

BY {label/product_id}, ALL OTHER

we let the system know that it can take this result and expand/repeat it across the other attributes (but only for the same product_id). now with this it is easy to define the final metric to multiply it by quantity and sum it up:

SELECT SUM({fact/quantity}*{metric/_last_product_price})

That should give you the calculation you asked for. You could potentially also do everything within a single complex metric.

SELECT SUM({fact/quantity} * (
    SELECT MAX(
        SELECT {fact/price}
          WHERE 
          (
          SELECT ROW_NUMBER((
              SELECT DATETIME_DIFF("2020-01-01 00:00", {label/sale_date.minute}, MINUTE)
                BY {dataset/fact_sales}))
            DESC
            WITHIN({label/product_id})) = 1)
      BY {label/product_id}, ALL OTHER))

I hope this helps.

Thanks a lot @Michal Hauzírek !