topic Re: Count the number of records with a specific Fractile value in QlikView

Count the number of records with a specific Fractile value

Tue, 14 Mar 2017 15:53:57 GMT

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 — Tue, 14 Mar 2017 16:11:25 GMT

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 — Tue, 14 Mar 2017 16:13:11 GMT

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

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

Tue, 14 Mar 2017 16:26:25 GMT

this appears to work perfectly.

thanks so much for your speedy answer!

Fraser