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

[AnshulKaaz]

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) )

[mageste · Accepted solution]

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.

[MarcoWedel · Accepted solution]

another solution might be

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