Hi everyone. Could you please help me with percentage calculation in LookML?
I need to count percentage of yes and no answers per question.
I received 100% for Yes and for No as well. But I wish to have result like No - 43%, Yes - 57%
My code is
Select distinct
RespondentId,
question_name,
yes_no
From Survey_Responses
Where question_name in ('sec2_5')
dimension: respondent_id {
hidden: yes
type: string
sql: ${TABLE}.lRespondentID ;;
}
dimension: question_name {
label: "Question Name"
type: string
sql: ${TABLE}.question_name;;
}
dimension: value {
label: "Yes/No"
type: string
sql: ${TABLE}.yes_no;;
}
measure: yes_no_total {
label: "Yes/No Total"
type: number
value_format: "#####"
sql: Count(${value});;
}
measure: ratio_total {
label: "% Ratio Yes/No"
type: number
value_format_name: percent_2
sql: ${yes_no_total}/nullifzero(Count(${value}));;
}
Solved! Go to Solution.
In this case you won’t be able to do it using LookML or table calculations becaue essentially what you need is a windowed sum partitioned by your question name. Window functions is something we can’t use. You can only achieve this by creating partitioned metrics in your data model or in a derived table:
https://community.looker.com/blog-archives-1027/a-window-into-the-soul-of-your-data-29363Here’s the explanataion why window functions don’t work. Basically dimensions are always in the GROUP BY clause. We’ve asked Looker long time ago to allow us to do that because it’s as simple as a parameter that would specify a dimension that is outside of the group by clause..
Here are two product ideas that could be upvoted to gain more traction: