How to divided a measure of type Sum by another measure of type Count

[Julie999]

Hi,

I have this measure in a derived table :

COALESCE(SUM(CASE WHEN ((DATE(sql_activity_days.sale_date )) - (DATE(sql_activity_days.membership_start_date )) < 122) THEN sql_activity_days.cad_net_invoiced ELSE NULL END), 0) AS “122_days”

I want to divided the sql_activity_days.cad_net_invoiced by this measure , How can I do that? If i just add ‘/member_count’ after ‘cad_net_invoiced’ is doesn’t work.

measure: member_count {
type: count_distinct
sql: ${TABLE}.email;;
}

The first derived table where I have the member_count is 🙂

SELECT
row_number() OVER(ORDER BY DATE_TRUNC(‘month’,d_member_preferences.membership_start_date )) AS prim_key,
DATE_TRUNC(‘month’, d_member_preferences.membership_start_date ) AS “styleship_start_month”,
d_member_preferences.membership_start_date As “membership_start_date”,
f_item_sold.member_id As “customer_id”,
f_item_sold.order_type_id As “Order_type_id”,
item_sold_date.full_date AS “sale_date”,
subscription_touch.cad_net_sold AS “cad_net_sold”,
f_item_sold.cad_net_invoiced AS “cad_net_invoiced”,
d_member.email,
COUNT(DISTINCT d_member.email ) AS “member_count”
FROM camel.f_subscription_touch AS subscription_touch
LEFT JOIN camel.f_item_sold AS f_item_sold ON f_item_sold.member_id =subscription_touch.member_id
LEFT JOIN camel.d_date AS start_date ON start_date.id = subscription_touch.start_date_id
LEFT JOIN camel.d_date AS item_sold_date ON item_sold_date.id = f_item_sold.sale_date_id
LEFT JOIN camel.d_member AS d_member ON d_member.id = subscription_touch.member_id
LEFT JOIN camel_pdt.LR$6P3L16CYIBLHGFSGMP0HH_d_member_preferences AS d_member_preferences ON d_member_preferences.member_id = d_member.id
INNER JOIN camel.d_order_type AS item_sold_order_type ON item_sold_order_type.id = f_item_sold.order_type_id
WHERE item_sold_order_type.bucket = ‘styleship’
GROUP BY d_member_preferences.membership_start_date,2,3,4,5,6,7,8,9;;

The second derived table where I have the measure sum.

SELECT
row_number() OVER( ) AS prim_key,
sql_activity_days.styleship_start_month As “start_month”
FROM camel.d_member AS d_member
COALESCE(SUM(CASE WHEN ((DATE(sql_activity_days.sale_date )) - (DATE(sql_activity_days.membership_start_date )) < 122) THEN sql_activity_days.cad_net_invoiced ELSE NULL END), 0) AS “122_days”
LEFT JOIN ${sql_activity_days.SQL_TABLE_NAME} As sql_activity_days ON sql_activity_days.customer_id = d_member.id
GROUP BY 2;;