topic Re: Fractile use? in QlikView

Fractile use?

Sat, 08 Oct 2011 13:34:08 GMT

Dear all,

I would like to create a graphic with the distribution of raw numeric data.
I think i have to use the 'fractile' function but I am not sure it's the correct way to do so.
Here is my need:
let say I have a field 'data' with 1000 values in incresing order.
I wish to graph on a single view the average value of the first 10% values (ie: 100 first values), then the average of the next 10% values (ie: 'data' from the 100th to the 200th value), then the average of the next 10% values (ie: 'data' from the 200th to the 300th value), etc... until 100% of 'data'.

So far, I tried to use a bar chart; with Dimension='Percentile' and expression=fractile(data,Percentile/100),
where Percentile is :

Percentiles:
LOAD * INLINE [
Percentil
10
20
30
40
50
60
70
80
90
100
];

As far as i undersatnd fractile function, it doesn't answer to my need... Could you please help me in this issue? (hope this is clear enough! )

Many Thanks,

Fractile use?

Sun, 09 Oct 2011 17:20:40 GMT

Does somebody has an idea ?

I was possibly thinking of using FieldValue function?

Re: Fractile use?

swuehl — Sun, 09 Oct 2011 17:38:40 GMT

Hi,

I think you could calculate your buckets and the average within each bucket in your script, maybe like this:

Data:

load

floor(RAND()*1000) as Value

AutoGenerate 1000;

let numRows = NoofRows('Data');

Result:

tmp:

Load Value

, ceil(recno()/$(numRows)*10)*10 as Percentile

resident Data order by Value asc;

Result:

Load

avg(Value) as AverageinBucket,

Percentile

resident tmp group by Percentile;

Please see attached sample.

Hope this helps,

Stefan

Fractile use?

Sun, 09 Oct 2011 22:21:40 GMT

Thanks, this is a way to do so (and works fine!).

According to my project, graph must be updated according to selected dimensions.

Indeed, 'Percentile' as to be "recomputed" according to number of 'Data' available...

Not very easy...

Re: Fractile use?

swuehl — Sun, 09 Oct 2011 23:37:52 GMT

Right, not easy.

Maybe you could use something like attached. I used some variables to hold the different fractiles and then check in charts if Values are within the bounderies determined by the fractiles.

The two charts at the bottom are sensitive to the possible state of the Values, so if you change the selection of Values, you will change the averages of the buckets.

Hope this helps,

Stefan