How to create a group by measure using LookML?


I have 2 steps of a calculated field -

First step, I created a derived table that calculate ccy_usd_amt grouped by cp_id, ccy, value_date:

view: aggregstion_base {
  derived_table: {
    sql:  SELECT
              SUM(ccy_usd_amt) as ccy_usd_amt_daily
          FROM (
              SELECT cp_id , ccy1 AS ccy , value_date , ccy1_usd_amt as ccy_usd_amt
              FROM dwh.fact_trades ft
              UNION ALL
              SELECT cp_id , ccy2 AS ccy , value_date , ccy2_usd_amt as ccy_usd_amt
              FROM dwh.fact_trades ft
              ) Union_step
          GROUP BY cp_id, ccy, value_date; ;;

Second step, using the derived table above, I need to create another aggregated SUM, grouped by cp_id, ccy.

How can I do it in LookML?


This is a cross dashboard measure, so it cannot be calculated as a table calculation.

0 replies

Be the first to reply!