Solved

How to ret percentage per value

  • 17 November 2022
  • 5 replies
  • 45 views

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

icon

Best answer by Dawid 17 November 2022, 10:17

View original

5 replies

Userlevel 7
Badge +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%


But received this instead:

Is there any way to have result aggregated per question_name?

Userlevel 7
Badge +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.

Reply