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

Julie999
Participant I

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

0 1 8,684
1 REPLY 1

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

Top Labels in this Space
Top Solution Authors