Question

LookML Dashboard generate ratio

  • 20 June 2022
  • 1 reply
  • 17 views

view: conversations {
sql_table_name: "PUBLIC"."CONVERSTATIONS"
;;

dimension: conversationId {
type: string
sql: ${TABLE}."CONVSERSATIONID" ;;
}

dimension: userId {
type: string
sql: ${TABLE}."USERID" ;;
}

dimension: phrase {
type: string
sql: ${TABLE}."PHRASE" ;;
}

dimension_group: endtime {
type: time
datatype: epoch
sql: ${TABLE}."ENDTIME"/1000 ;;
}

dimension_group: starttime {
type: time
datatype: epoch
sql: ${TABLE}."STARTTIME"/1000 ;;
}

dimension: conversationDuration {
type: number
sql: ${endtime_raw} - ${starttime_raw} ;;
}

measure: count {
type: count_distinct
sql: ${conversationId} ;;
}
}

Using this view I can create a lookML dashboard to get the total `count` of conversations per `userId`. Let’s call this `count_per_user`.
I can also add a `phrase` filter and get the total `count` per `userId`.  Let’s call this `phrase_count_per_user`.

Is there a way I can get the ratio of `count_per_user` / `phrase_count_per_user` on a looker dashboard using some lookML? I want to enable filters on `userId` and `phrase`. 


1 reply

Userlevel 7
Badge +1

Add a measure count_phrases

measure: count_phrases {
type: count_distinct
sql: ${phrase} ;;
}

And then a measure of type number

measure: phrases_per_user {
type: number
sql: ${count_phrases} / GREATEST(${count_users}, 1) ;;
}

 

You may need to replace GREATEST with SAFE_DIVIDE or another form of making sure you won’t divide by zero

Reply