Count the number of records with a specific Fractile value

[#1]

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:

load * Inline [

  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

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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

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

[#4]

this appears to work perfectly.

thanks so much for your speedy answer!

Fraser