Question

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

  • 9 August 2018
  • 1 reply
  • 5276 views

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;;


1 reply

Userlevel 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.




In your case, we would want to use dimensions since your aggregate functions are from derived tables. If I understand correctly, you would like to divide ```sql_activity_days.cad_net_invoiced``` in _derived table 2_ by ```COUNT(DISTINCT d_member.email ) AS "member_count"``` from _derived table 1_.

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

(...)

}



If you would like us to look at your specific use case further, shoot us an email at help.looker.com.

Cheers,

Adina

Reply