Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Count the number of records with a specific Fractile value

Hello clever folks,

I am having some trouble with a formula to know the exact number of records that have a specific fractile() value.

for this data:

Test_data:

order, tat

A100, 2

A101, 3

A103, 1

A104, 1

A105, 4

A106, 5

A107, 1

A108, 7

A109, 6

A110, 3

A111, 1

A112, 4

A113, 1

];

If i now in a text box have the formula ROUND(FRACTILE(tat,0.9)) I will get a value of 6

What I want to know is how many orders belong to that value.  This should be 12 (I can do this in Excel to validate)

I was trying things like COUNT(AGGR(ROUND(FRACTILE(tat,0.9)),order)), but that is only giving me 13, the total number of records.

any one have a good tip for me?

thanks very much

Fraser

1 Solution

Accepted Solutions
MVP

My bad, missed the if statement... try this

=Sum(Aggr(If(Round(Fractile(TOTAL tat, 0.9)) >= tat, 1, 0), order))

3 Replies
MVP

Based on the above sample you expect to see 12? or is the 12 for your actual sample? May be you need this

Sum(Aggr(Round(Fractile(TOTAL tat, 0.9)) >= tat, 1, 0), order))

MVP

My bad, missed the if statement... try this

=Sum(Aggr(If(Round(Fractile(TOTAL tat, 0.9)) >= tat, 1, 0), order))

Not applicable
Author

this appears to work perfectly.