Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
My bad, missed the if statement... try this
=Sum(Aggr(If(Round(Fractile(TOTAL tat, 0.9)) >= tat, 1, 0), order))
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))
My bad, missed the if statement... try this
=Sum(Aggr(If(Round(Fractile(TOTAL tat, 0.9)) >= tat, 1, 0), order))
this appears to work perfectly.
thanks so much for your speedy answer!
Fraser