Percentage calculation and user clustering

[kateryna_dalako]

Hello! I am trying to do a few thing in 1 table:

1. calculate percentage from Total Dollar spend despite the filters change (so the % is not constant 100%, related issue is here) AND
2. at the same time filter the table based on common pool of User IDs between the filtered value and a table. The expected results after filtering attaching as a video. For example, when you select “Automotive” category filter the table will show you other categories where the same pool of User ID was noticed & will give you % from the Total Dollar Spend within the category.

Note:

• % calculation is successful, but when it comes to the User ID filtering things get more complicated. Here is how the % was calculated:

  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)

• encrypted_consumer_id is the primary key

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!