How to ret percentage per value

Looker Q&A Looker
1

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}));;
}

1 Like
2

Your yes_no_total field is not a columnar total, it’s still an aggregation that totals the value per row. Instead choose one of the predefined calculations to get this result - % of column:

1 Like
3

@Dawid Thank you for your suggestion. Unfortunately this solution does not work once I added one more question_name. I need to show percentage from total amount of answers per question_name. Like this:

sec2_5 Yes 16170 57%
sec2_5 No 12233 43%
sec2_6 Yes 22297 77%
sec2_6 No 6097 21%

But received this instead:

Is there any way to have result aggregated per question_name?

4

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-29363

Here’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:

https://portal.feedback.us.pendo.io/app/#/case/24417

https://portal.feedback.us.pendo.io/app/#/case/115822

4 Likes
5

I’ve found not being able to use windowing functions directly in Looker a big drawback. Not a deal breaker, but not far away from it. I’m surprised it hasn’t been addressed.

1 Like
6

@Dawid Thank you much. Will dealing with window functions in sql query then.