A financial analyst reached out to me and asked that I implement a measure to an Explore he uses frequently.
This measure "True Cost" is a simple division based upon the "breakdown type" of the transaction. In SQL, it goes like this:
SELECT
CASE
WHEN BREAKDOWN_TYPE IN (
'Scheme Fixed Fee',
'Premium Variable Fee',
'Gateway Fixed Fee',
'Interchange Fixed Fee',
'Chargeback Fixed Fee',
'Scheme Variable Fee'
) THEN SUM(
PROCESSING_CURRENCY_AMOUNT
) END
/
CASE WHEN BREAKDOWN_TYPE = 'Capture' THEN SUM(PROCESSING_CURRENCY_AMOUNT) END AS TRUE_COST
FROM
"FINANCIAL_ACTIONS"
GROUP BY BREAKDOWN_TYPE;
But when I implemented it in my model, it failed to run with the following error:
SQL compilation error: Aggregate functions cannot be nested:
COALESCE(SUM(CASE WHEN financial_actions."BREAKDOWN_TYPE" IN ( 'Scheme Fixed Fee', 'Premium Variable Fee', 'Gateway Fixed Fee', 'Interchange Fixed Fee', 'Chargeback Fixed Fee', 'Scheme Variable Fee' ) THEN SUM(financial_actions."PROCESSING_CURRENCY_AMOUNT") END / CASE WHEN financial_actions."BREAKDOWN_TYPE" = 'Capture' THEN SUM(financial_actions."PROCESSING_CURRENCY_AMOUNT") END ), 0) AS "financial_actions.true_cost"
The error message does make sense but I don't see where I went wrong in my LookML:
measure: true_cost {
type: sum
value_format_name: usd
sql: CASE
WHEN ${TABLE}."BREAKDOWN_TYPE" IN (
'Scheme Fixed Fee',
'Premium Variable Fee',
'Gateway Fixed Fee',
'Interchange Fixed Fee',
'Chargeback Fixed Fee',
'Scheme Variable Fee'
) THEN SUM(${TABLE}."PROCESSING_CURRENCY_AMOUNT")
END / CASE
WHEN ${TABLE}."BREAKDOWN_TYPE" = 'Capture' THEN SUM(${TABLE}."PROCESSING_CURRENCY_AMOUNT")
END ;;
How can I work around this issue? Should I create a dimension for the numerator and another one for the denominator?
Thank you!