Percentile as Dynamic User input

calculations

(Suresh Prabhu Botharaj) #1

I would like to create a measure which dynamically gets the N (e.g. 90, 95, 99 etc) from the user and calculates the Nth percentile of a dimension (e.g. count_dimension).

measure: display_Nth_percentile {
group_label: “Output”
type: percentile
percentile: should be dynamically fetched from user input
sql: ${TABLE}.count_dimension
}

percentile: {% parameter input_percentile %} does not seem to work.

Of course, the following works but is restricting the user to choose only 50, 75, 90 etc.
measure: display_50_percentile {
group_label: “Percentile”
label: “p50”
type: percentile
percentile: 50
sql: ${TABLE}.{% parameter latency_dimension %} ;;
}
measure: display_75_percentile {
group_label: “Percentile”
label: “p75”
type: percentile
percentile: 75
sql: ${TABLE}.{% parameter latency_dimension %} ;;
}
measure: display_90_percentile {
group_label: “Percentile”
label: “p90”
type: percentile
percentile: 90
sql: ${TABLE}.{% parameter latency_dimension %} ;;
}

Any guidance is appreciated.


(Sara Guzman) #2

Hi @sbotharaj
Have you tried adding the last % to the code: percentile: {% parameter input_percentile}

Example: {% parameter input_percentile %}

Please let us know if this works for you.


(Suresh Prabhu Botharaj) #3

@Sara_Guzman Apologies that’s a typo in the post. It does n’t work.

image


(Ian) #4

This is not ideal but you can just get it working right now and wait for a better solution by setting the type to number and putting the percentile function (including the liquid param) into the sql attribute.


(Suresh Prabhu Botharaj) #5

Thank you @IanT. I have settled down now with separate measures for p99, p95, p75 and p50 for the users to choose. I will try your suggestion when the time permits.