Hey,
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
cp_id,
ccy,
value_date,
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.