topic Re: how to count the number of records supporting a FRACTILE in QlikView

how to count the number of records supporting a FRACTILE

Wed, 30 Jul 2014 18:23:53 GMT

Hi,

Another newbie question for the experts...

I am using the FRACTILE function to determine the 90% fill rate on a volume of order line items.

=FRACTILE(ORDER_TAT,0.9)

which returns me a value of 3.0

What I want to do now is see how many orders make up this value.

I thought something like:

=COUNT(FRACTILE(ORDER_TAT,0.9),order_no)

but this does not work.... I know what the values should be based on examination of the raw data in Excel. The number of records will change from month to month, so I want to calculate my fractile value (which is in days) and see the number of records supporting that.

Anyone have an idea on what I could do?

thanks,

Re: how to count the number of records supporting a FRACTILE

rwunderlich — Wed, 30 Jul 2014 18:41:36 GMT

=COUNT(if ORDER_TAT <= FRACTILE(ORDER_TAT,0.9),order_no)

-Rob

Re: how to count the number of records supporting a FRACTILE

Wed, 30 Jul 2014 18:56:59 GMT

Error in that expression... is there some syntax missing around if?

Re: how to count the number of records supporting a FRACTILE

simenkg — Wed, 30 Jul 2014 19:16:12 GMT

=COUNT(if(ORDER_TAT <= FRACTILE(ORDER_TAT,0.9),order_no))

Some missing parenthesis.

Re: how to count the number of records supporting a FRACTILE

Wed, 30 Jul 2014 19:23:28 GMT

This looks perfect to me, but I still get "error in expression"

I tried taking out the spaces around the <= as well with no luck.

very frustrating.....

Re: how to count the number of records supporting a FRACTILE

simenkg — Wed, 30 Jul 2014 19:33:48 GMT

How about:

COUNT(if(ORDER_TAT <= FRACTILE(Total ORDER_TAT,0.9),order_no))

Re: how to count the number of records supporting a FRACTILE

Wed, 30 Jul 2014 19:39:26 GMT

that works perfectly.

You sir are a genius.

Re: how to count the number of records supporting a FRACTILE

simenkg — Wed, 30 Jul 2014 19:41:12 GMT

Glad I could help