Question

How can I calculate percentile?

  • 9 November 2021
  • 0 replies
  • 35 views

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

 


0 replies

Be the first to reply!

Reply