How to ret percentage per value

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%

 

0fe1b7e6-8d17-4172-9e1e-1c87130371d7.png

   
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 Solved
1 5 619
1 ACCEPTED 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-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

View solution in original post

5 REPLIES 5
Top Labels in this Space
Top Solution Authors