How can I calculate percentile?

[Douglas_Souza]

Hi guys! 

I need to calculate percentile in my dashboard, but I need to estimate this in the range date. 

e.g., Percentile has to be calculate per week like this SQL query 

with query_RT
AS 
( 
    select a.issue_id, b.created_at, a.from_status, a.to_status, b.project, b.key, a.minutes_to_first_response, a.minutes_to_resolution,
    240 - a.minutes_to_resolution AS RT_vs_SLA,
    from `nu-br-data.dataset.jira_issues_status_history` as a
    inner join `nu-br-data.dataset.jira_issues` as b on a.issue_id = b.issue_id
    where a.to_status = "Resolved"
    and b.project = "FSD"
    and created_at >= "2021-10-29"
and created_at <= "2021-11-04"
    order by b.key desc    
), 
query_RT_2
AS 
(
    select issue_id, key, to_status, created_at, RT_vs_SLA,
    percent_rank() OVER (ORDER BY RT_vs_SLA) AS Percentil_RT
    from query_RT 
)
select * from query_RT_2 
where Percentil_RT <=0.75 
order by key desc

First of all, I filter the range date that I would like, after that, I estimate the percentile and finally I filter percentile lower than 0.75. 

How I can do this in Looker, because I need to filter before I estimate the percentile. Because only percentile of the select range date is important at the moment. 

I have tried to solve this with LookML, but doesn't work as well 

explore: jira_issue {
  group_label: "BR - Platform"
  label: "Jira Issue"
}
view: jira_issue {


#   # Or, you could make this view a derived table, like this:
   derived_table: {
     sql: SELECT
    a.issue_id, b.created_at, a.from_status, a.to_status, b.project, b.key, a.minutes_to_first_response, a.minutes_to_resolution,
    60 - a.minutes_to_first_response AS FRT_vs_SLA,
    240 - a.minutes_to_resolution AS RT_vs_SLA
    from `nu-br-data.dataset.jira_issues_status_history` as a
    inner join `nu-br-data.dataset.jira_issues` as b on a.issue_id = b.issue_id
    where minutes_to_first_response <> 0
    and b.project = "FSD"
       ;;
   }
#
#   # Define your dimensions and measures here, like this:
  dimension: issue_id {
     type: string
     sql: ${TABLE}.issue_id ;;
   }
  dimension: created_at {
    type: date
    sql: ${TABLE}.created_at ;;
  }
  dimension: from_status {
    type: string
    sql: ${TABLE}.from_status;;
  }
  dimension: to_status {
    type: string
    sql: ${TABLE}.to_status;;
  }
  dimension: project {
    type: string
    sql: ${TABLE}.project;;
  }
  dimension: key {
    type: string
    sql: ${TABLE}.key;;
  }
  dimension: minutes_to_first_response {
    type: number
    sql: ${TABLE}.minutes_to_first_response;;
  }
  dimension: minutes_to_resolution {
    type: number
    sql: ${TABLE}.minutes_to_resolution;;
  }
  dimension: FRT_vs_SLA {
    type: number
    sql: ${TABLE}.FRT_vs_SLA;;
  }
  dimension: RT_vs_SLA {
    type: number
    sql: ${TABLE}.RT_vs_SLA;;
  }

  measure: percentile_FRT_75 {
     type: percentile
     percentile: 75
     sql: ${TABLE}.FRT_vs_SLA ;;
   }
  measure: percentile_RT_75 {
    type: percentile
    percentile: 75
    sql: ${TABLE}.RT_vs_SLA ;;
  }
 }