Transactions

view: transactions {
  sql_table_name: public.transactions ;;

  dimension: id {
    type: int
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: adid {
    type: int
    sql: ${TABLE}.adid ;;
  }

  dimension: clientip {
    sql: ${TABLE}.clientip ;;
  }

  dimension: commissionvalue {
    type: number
    hidden: yes
    sql: ${TABLE}.commissionvalue ;;
  }

  dimension: couponid {
    type: int
    sql: ${TABLE}.couponid ;;
  }

  dimension: currency {
    sql: ${TABLE}.currency ;;
  }

  dimension: details {
    sql: ${TABLE}.details ;;
  }

  dimension: orderid {
    sql: ${TABLE}.orderid ;;
  }

  dimension: ordertotal {
    type: number
    value_format: "#,##0.00"
    sql: ${TABLE}.ordertotal ;;
  }

  dimension: partnerid {
    sql: ${TABLE}.partnerid ;;
  }

  dimension: partnersaleid {
    sql: ${TABLE}.partnersaleid ;;
  }

  dimension: paymenttype {
    sql: ${TABLE}.paymenttype ;;
  }

  dimension: productid {
    type: int
    sql: ${TABLE}.productid ;;
  }

  dimension: program {
    sql: ${TABLE}.program ;;
  }

  dimension: referer {
    sql: ${TABLE}.referer ;;
  }

  dimension: sessionid {
    sql: ${TABLE}.sessionid ;;
  }

  dimension: status {
    sql: ${TABLE}.status ;;
  }

  dimension: userid {
    sql: ${TABLE}.userid ;;
  }

  dimension_group: lastupdate {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.lastupdate ;;
  }

  dimension_group: partnertrackingtime {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.partnertrackingtime ;;
  }

  dimension_group: transactiondate {
    type: time
    timeframes: [time, date, week, month]
    convert_tz: no
    sql: ${TABLE}.transactiondate ;;
  }

  measure: total_calculated_commission {
    type: sum
    value_format: "#,##0.00"
    sql: case ${affiliate_networks.name} when '<% parameter partner_name1 %>' then ${transactions.ordertotal}*0.05 when 'Lomadee' then ${transactions.ordertotal}*0.0 when '<% parameter partner_name2 %>' then ${transactions.ordertotal}*0.12 when '<% parameter partner_name3 %>' then ${transactions.ordertotal}*0.06 else ${transactions.commissionvalue} end ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: total_commissionvalue {
    type: sum
    value_format: "#,##0.00"
    sql: ${commissionvalue} ;;
  }

  measure: total_transactionvalue {
    type: sum
    value_format: "#,##0.00"
    sql: ${ordertotal} ;;
  }
}

- view: transactions
  sql_table_name: public.transactions
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: adid
    type: int
    sql: ${TABLE}.adid
  - dimension: clientip
    sql: ${TABLE}.clientip
  - dimension: commissionvalue
    type: number
    hidden: true
    sql: ${TABLE}.commissionvalue
  - dimension: couponid
    type: int
    sql: ${TABLE}.couponid
  - dimension: currency
    sql: ${TABLE}.currency
  - dimension: details
    sql: ${TABLE}.details
  - dimension: orderid
    sql: ${TABLE}.orderid
  - dimension: ordertotal
    type: number
    value_format: '#,##0.00'
    sql: ${TABLE}.ordertotal
  - dimension: partnerid
    sql: ${TABLE}.partnerid
  - dimension: partnersaleid
    sql: ${TABLE}.partnersaleid
  - dimension: paymenttype
    sql: ${TABLE}.paymenttype
  - dimension: productid
    type: int
    sql: ${TABLE}.productid
  - dimension: program
    sql: ${TABLE}.program
  - dimension: referer
    sql: ${TABLE}.referer
  - dimension: sessionid
    sql: ${TABLE}.sessionid
  - dimension: status
    sql: ${TABLE}.status
  - dimension: userid
    sql: ${TABLE}.userid
  - dimension_group: lastupdate
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.lastupdate
  - dimension_group: partnertrackingtime
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.partnertrackingtime
  - dimension_group: transactiondate
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.transactiondate
  - measure: total_calculated_commission
    type: sum
    value_format: '#,##0.00'
    sql: case ${affiliate_networks.name} when '<% parameter partner_name1 %>' then ${transactions.ordertotal}*0.05 when 'Lomadee' then ${transactions.ordertotal}*0.0 when '<% parameter partner_name2 %>' then ${transactions.ordertotal}*0.12 when '<% parameter partner_name3 %>' then ${transactions.ordertotal}*0.06 else ${transactions.commissionvalue} end
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: total_commissionvalue
    type: sum
    value_format: '#,##0.00'
    sql: ${commissionvalue}
  - measure: total_transactionvalue
    type: sum
    value_format: '#,##0.00'
    sql: ${ordertotal}