Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Find Average of last 25% of data set in a column.

Hi , I have a data set (sample below). I want to find the average of the first 25 percentile values , can someone please help with inputs:

Dim1 , Val 1 , Flag

A, 100,1

B, 20,1

C, 30,0

D,40,0

E,78,1

F,129,1

G,65,1

H,25,1

I,100,1

I was trying to Use the below approach but nested aggregations are not supported:

Avg( {<Flag = {1}>} If(Val1 <  fractile({<Flag  = {1}>}Val1 , 0.25) , Val1) )

Labels (1)
• ### General Question

2 Solutions

Accepted Solutions
Partner - Contributor III

Hello there!

I think you can handle the nested aggregation using TOTAL inside the Fractile expression.

I tried this way and it worked in my example:

Avg( {<Flag = {1}>} If("Val 1" < fractile({<Flag = {1}>} TOTAL "Val 1" , 0.25) , "Val 1") )

Please let me know if it helps.

MVP

another solution might be

``=Avg({<Flag={1},Val1={"=Val1<Fractile({<Flag={1}>} TOTAL Val1,0.25)"}>} Val1)``
2 Replies
Partner - Contributor III

Hello there!

I think you can handle the nested aggregation using TOTAL inside the Fractile expression.

I tried this way and it worked in my example:

Avg( {<Flag = {1}>} If("Val 1" < fractile({<Flag = {1}>} TOTAL "Val 1" , 0.25) , "Val 1") )

Please let me know if it helps.

MVP

another solution might be

``=Avg({<Flag={1},Val1={"=Val1<Fractile({<Flag={1}>} TOTAL Val1,0.25)"}>} Val1)``