Hello! I am trying to do a few thing in 1 table:
Note:
derived_table: {
sql: SELECT t1.Days_in_Date, retailer_name, total_spent, total_dollars_spent
FROM ( SELECT Days_in_Date, retailer_name, SUM(dollars_spent) as total_spent FROM `pay-eq.prod_pay.t_pay_transactions`
group by Days_in_Date,retailer_name)
as t1 join (SELECT Days_in_Date, SUM(dollars_spent)
as total_dollars_spent FROM `pay-eq.prod_pay.t_pay_transactions` group by Days_in_Date)
as t2 ON t1.Days_in_Date = t2.Days_in_Date
where {% condition retailer_filter %} retailer {% endcondition %} and (t1.retailer_name ) <> "N\\A"
;;
}
filter: retailer_filter {
type: string
suggest_dimension: retailer_name
}
...and after that just a Table calculation (total_spend / total_dollar_spend * 100)
Given Data
Views:
include: "/views/**/t_pay_transactions.view"
include: "/views/**/t_pay_users.view"
include: "/views/**/t_pay_accounts_bcc.view”
Model (just a part where tables are being connected):
explore: t_pay_users {
label: "Pay All"
join: t_pay_transactions {
type: left_outer
relationship: one_to_many
sql_on: ${t_pay_transactions.encrypted_consumer_id} = ${t_pay_users.encrypted_consumer_id} ;;
}
join: t_pay_accounts_bcc {
type: left_outer
relationship: one_to_many
sql_on: ${t_pay_accounts_bcc.encrypted_consumer_id} = ${t_pay_users.encrypted_consumer_id} ;;
}
}
Table 1: t_pay_transactions.view ( sql_table_name:`prod_pay.t_pay_transactions`)
Fieds 1:
view: t_pay_transactions {
dimension: account_type {
label: "Account Type"
type: string
sql: ${TABLE}.account_type ;;
}
dimension: category_name {
label: "Category"
type: string
sql: ${TABLE}.category_name;;
}
dimension_group: days_in {
label: "Transaction"
type: time
timeframes: [
#raw is default
raw,
date,
week,
day_of_week,
day_of_week_index,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.Days_in_Date ;;
}
dimension: dollars_spent {
label: "Dollar Spend"
type: number
sql: safe_cast(${TABLE}.dollars_spent as FLOAT64) ;;
}
measure: total_dollars_spent {
label: "Dollar Spend"
type: sum
sql: ${dollars_spent} ;;
value_format_name: usd_0
}
measure: average_dollars_spent_user{
label: "Average Dollars Spent per User"
type: number
sql: ${total_dollars_spent}/NULLIF(${unique_encrypted_consumer_id},0)*1.0 ;;
value_format_name: usd_0
drill_fields: [days_in_date,account_name,
institution_name,
retailer_name,category_name,
average_dollars_spent_user]
}
dimension: encrypted_consumer_id {
label: "User ID"
type: string
hidden: yes
sql: ${TABLE}.encrypted_consumer_id ;;
}
measure: unique_encrypted_consumer_id {
label: "Customers"
type: count_distinct
sql: ${encrypted_consumer_id} ;;
value_format_name: decimal_0
}
dimension: no_of_transactions {
label: "Number of Transactions"
type: number
sql: ${TABLE}.no_of_transactions ;;
}
measure: total_no_of_transactions {
label: "Total Number of Transactions"
type: sum
sql: ${no_of_transactions} ;;
value_format_name: decimal_0
drill_fields: [days_in_date,account_name,
institution_name,
retailer_name,category_name,
total_no_of_transactions]
}
measure: average_transac_user{
label: "Average Transactions per User"
type: number
sql: ${total_no_of_transactions}/NULLIF(${unique_encrypted_consumer_id},0)*1.0 ;;
value_format_name: decimal_2
drill_fields: [days_in_date,account_name,
institution_name,
retailer_name,category_name,
average_transac_user]
}
dimension: retailer_name {
label: "Retailer Name"
type: string
sql: ${TABLE}.retailer_name;;
}
}
Table 2: t_pay_users.view ( sql_table_name:`prod_pay.t_pay_users`)
Fieds 2:
view: t_pay_users {
sql_table_name: `prod_pay.t_pay_users`;;
dimension: consumer_gender {
label: "Gender"
type: string
sql: ${TABLE}.consumer_gender ;;
}
dimension: consumer_profile_province {
label: "Province"
type: string
sql: ${TABLE}.consumer_profile_province ;;
}
dimension: encrypted_consumer_id {
primary_key: yes
label: "User ID"
type: string
hidden: yes
sql: ${TABLE}.encrypted_consumer_id ;;
}
}
Table 3: t_pay_accounts.view ( sql_table_name:`prod_pay.t_pay_accounts`)
Fieds 3:
view: t_pay_accounts {
sql_table_name: `prod_pay.t_paymi_accounts`
;;
dimension: account_type {
label: "Account Type"
type: string
sql: ${TABLE}.account_type;;
}
dimension: encrypted_consumer_id {
primary_key: yes
label: "User ID"
type: string
hidden: yes
sql: ${TABLE}.encrypted_consumer_id ;;
}
dimension: institution_name {
label: "Institution Name"
type: string
sql: ${TABLE}.institution_name ;;
}
}
I tried my best in explaining the problem, but if something is unclear please let me know!