Hi Have reviewed the posts on fractiles being used instead of percentiles, and i have concluded that this is what i need, BUT i am having some issues with syntax, my current expression looks like so....
=fractile((count( {<Result= {PASS}>} TOTAL <Campaign, SiteName, Categorization_Tier_1, [User Login Name]> Result)/count( TOTAL <Campaign, SiteName, Categorization_Tier_1, [User Login Name]> Result)),.25)
this does not produce a result, maybe you all already know why?. So anyway i have two questions..
(1) is there a way one could verify wheather this function works correctly, most expressions i have created i have been able to validate with reasonable ease, this one i just can not seem to understand how it works.
(2) the help does not really discript where fractile function would be used best pivot straight table etc. Any ideas on how to correctly utilise would be great.
1) I don't think there's any such thing as THE definition of fractile. You just get various implementations. That said, they should all have some very similar features. I'm not sure if it's the best reference, but here are the wikipedia articles on percentiles and quantiles:
2) It entirely depends on your requirements. You use fractile() where you need fractile(), just like you use sum() where you need sum(), and use avg() where you need avg(). As for how I'VE used it, I built a fake box-and-whisker plot for the number of days for different events to occur, indicating the minimum value, first quartile, median, upper quartile, and maximum value. Fractile allowed me to calculate the quartiles. I know I've used it elsewhere, but I'm not remembering where.
Hi Alan, I think this question is right up my alley as I've used fractile a few times in the past.
The thing with fractiles is that to be able to provide a result you actually need to calculate every value and then get the one corresponding to that fractile of the population.
i.e. if I have 5 values of sales 10, 15, 20, 25, 50, fractile (sales, 0.5) will equal 20 (I need the 5 values to come up with the 0.5 fracile).
If you need to find a fractile over a sum of sales for products then you can't just do sum(sales) or sum(total sales) because that won't produce a list of values, just one. What you need to do is use aggr to summarize by a dimension and then get a list of values.
So you would need to do something like fractile(aggr(sum(sales),product),0.5) to first get a result for each product, then QlikView will sort them and give you the value corresponding to the 0.5 fractile
I hope thats clear enough, I know I've read it before in the help so you can also check it there.
I have a situation where I want to discard outliers in a data range (say the bottom and top 10%) and then calculate the first quartile of the remaining set. Based on what you said here, I get the fractile bit but do you have any idea how I would combine it with first removing the outliers?
