Average of the values with the 90th Percentile

Question & Answer
, ,
1

Scenario
Data Structure - Case ID, Created Date, resolved date , time taken to resolve ( resolved date - created date)

Step 1 - Getting all the ttr’s in a specific range of resolved date - daterangeresolved
ifelse(

extract(“YYYY”, {resolved_date}) = extract(“YYYY”, now()),{ttr_real},NULL)

Gets all the ttr in the specified year, applied the filter to get it for a specific month

step 2 - Calculate the 90th percentile value tpr_m

percentile(DateRangeResolved,0.9)

Step 3 - Create a new calculated field to check if the ttr value is less than tpr_m

ifelse(DateRangeResolved <= tprM ,1,0)

This is where I keep getting the error
Nesting of aggregate functions like AVG and AVG(PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY “47705b31-8411-4e20-b70f-c9f61b8cbe71”)) is not allowed.

Kindly help me in implementing this metric

2

Hello @Sriram_Sai_Amuluru !

To clarify, are you getting that error when you try to save the calculated field or when you try to add it to your visualization?

And if the error is coming from the visualization, what kind of visualization are you applying it to, for instance a table or a pivot table?

3

While trying to save the calculated field

4

Hello @Sriram_Sai_Amuluru !

I think the following set up should work:

ttr_real_filtered:

ifelse(
extract("YYYY", {resolved_date}) = extract("YYYY", now()) AND
extract("MM", {resolved_date}) = 1,  
{ttr_real},
NULL)

tpr_m: percentile(ttr_real_filtered, 0.9)

Final calc: ifelse({ttr_real} <= tpr_m, 1, 0)