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;;
Hi Julie,
In general, measures of type number can be used to divide a measure of type sum by another measure of type count. The syntax for this is:
measure: div_measure {
type: number
sql: ${view_name.sum_measure} / ${view_name.count_measure} ;;
}
This doc discusses the different types of Looker measures.
You can achieve this by creating a dimension for member_count
in the view that contains derived table 1 and a dimension for 122_days
in the view that contains derived table 2 and then you could create a dimension that divides 122_days
by member_count
.
This would look something like:
VIEW_1_NAME.view.lkml
view: view_1_name {
derived table: {
sql: SELECT * FROM
( ... ) as table_1_name
}
dimension: 122_days {
type: number
sql: {TABLE}.122_days
}
dimension:
type: number
sql: ${122_days} / ${view_2_name.member_count}
}
VIEW_2_NAME.view.lkml
view: view_2_name {
derived table: {
sql: SELECT * FROM
( ... ) as table_2_name
}
dimension: member_count {
type: number
sql: {TABLE}.member_count
}
}
Note: since we are referencing a field from view_2 in view_1, these two views will need to be joined in the explore associated with view_1. This should be done in the model file, like so:
explore: view_1_name {
join: view_2_name
(...)
}
Cheers,
Adina