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`.
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