Solved

# How to ret percentage per value

• 5 replies
• 154 views

• Member
• 3 replies

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

icon

Best answer by Dawid 17 November 2022, 10:17

View original

This topic has been closed for comments

### 5 replies

Userlevel 7
+1

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:

@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%

Is there any way to have result aggregated per question_name?

Userlevel 7
+1

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:

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

Userlevel 2

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.

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