Derived table SQL: Standardising test to control in Looker dashboard

[eleanor_bill-16]

Hi,

I’m building a Looker dashboard and in it I’d like to standardise my test values to control spend (at the moment we’re standardising to total spend) - e.g. for clicks we’d like to move from:

normalised clicks = clicks * sum(total spend) / 2 * total spend

to

normalised clicks = clicks * sum(control spend) / 2 * control spend

I’m therefore trying to figure out how to calculate control spend in Looker and then apply that control spend value to the rest of the columns so they can use it in those calculations too. For example if I had more than one SQL step I would do something similar to the following:

CREATE TABLE temp_1 as
SELECT TEST_OR_CONTROL,
COALESCE(SUM((CLICKS)), 0) AS total_clicks,
NULL as total_control_cost_usd
FROM datatable1
<WHERE STATEMENT HERE>
GROUP BY 1

INSERT INTO temp_1.total_control_cost_usd
VALUES (
SELECT DISTINCT total_control_cost_usd FROM
(select TEST_OR_CONTROL,
COALESCE(SUM((CASE WHEN TEST_OR_CONTROL = 'Control Group' THEN "COST_USD"* 1.2 
ELSE NULL 
END)), 0) AS total_control_cost_usd
FROM datatable1
<WHERE STATEMENT HERE>)
GROUP BY 1

Is there a way to do this in Looker views, either in SQL or LookerML? I’d prefer to do it as a derived table ideally, but seems a bit tricky.

If not, I’ll have a look at pivot table calculations.

Thanks.