topic Re: rangefractile and group by in QlikView

rangefractile and group by

Thu, 12 Feb 2015 16:34:06 GMT

I am trying (unsuccessfully) to do this in my load script:

load

%Stock#,

// rangefractile(fractile, expr1 [ , expr2, ... exprN ])

rangefractile(0.25,[Lead time (days)]) as [LBound],

rangefractile(0.75,[Lead time (days)]) as [UBound]

resident

factPOReceipts

group by

%Stock#

;

Every Stock# has been ordered multiple times and each time the lead time from order date to goods received differs. I want to figure out the 25th and 75th percentiles of the lead times per stock#. I have tried fractile and rangefractile and can get neither to work for me.

I assume for the rangefractile to work the table must be grouped or ordered by stock#.

Is this possible?

Oli

Re: rangefractile and group by

Oleg_Troyansky — Thu, 12 Feb 2015 16:43:38 GMT

Hi Oli,

Rangefractile() can be used on a list of distinct values, like this:

Rangefractile(0.25, v1, v2, v3, v4, v5)

So, for your purposes, Fractile() is certainly the function to use. However, fractile() places the percentage as the last parameter and not the first:

fractile([Lead time (days)], 0.25) as [LBound],

fractile([Lead time (days)], 0.75) as [UBound]

could this be your problem? If it still doesn't work, I'd check whether these statistical functions can be used in the script or maybe they are only available in chart calculations...

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

Re: rangefractile and group by

Thu, 12 Feb 2015 16:52:32 GMT

Hi Oleg

Yes, I had noticed the difference in parameter location. Seems a strange think for the developers to have done?

I went back to fractile as you have suggested and seems to have worked now. I wonder if I was sorting rather than grouping last time.

Thank you for your help (and speed)