Average of fractile

[nigel987]

Hi,

I want to calculate the average of all values below the 98th fractile.

In my example I have a counter (called impressions) and I want to calculate the average of all counters cutting off the top 2 percent.

I tried the expression

=Avg(If(impressions < Fractile(impressions,0.95),impressions))

But I receive the error "Allocated memory" exceeded. I do not know why.

Does someone have any idea how I could solve this?

I also tried to find a function which returns values which match a certain condition (in my case being lower than a fractile), so that I could use it for the average caluclation, but I wasn't successful.

Appreciate any hint!

Thanks, Nigel

[sunny_talwar · Accepted solution]

Try this:

=Avg(Aggr(If(impressions < Fractile(TOTAL <jobcreationdateJahrMonat> impressions,0.98), impressions), jobcreationdateJahrMonat, jobid))

[nigel987]

Hi Sunny, that gives the correct result! I would have never guessed that I need the jobid in the aggregation!

Many thanks!

[swuehl]

That's because the aggr() dimension values are distinct values, so if there are duplicate impression values per month, grouping by impression won't give you the required result.

[nigel987]

Thanks for the information!