frequency distribution calculation

[#1]

Hi

Thought it trivial but I got stuck. Given the following example numbers

Range_From, Range_To, Counter
0, 5, 3
5, 10, 13
10,15,28
15, 20,12
20, 25, 7

how does the formula in qlik have to look to get me the 16% percentile (sum(Counter) = 63),?

I can manually calculate it (assuming even distribution in the range) to be 7.7 but I would need to get the correct range row first (2) to calculate:

[Range_From] + ([Percentile] - [Range_Start %] / [Range%] * [Range-Width].
or 5 + (16 - (3/63*100) / (13/63*100) * 5)

Thanks for any help in advance

Jürg

[johnw]

I'm not sure exactly what you're asking, but the 16th percentile should be fractile(RawValue,.16). It's almost always a bad idea to pre-summarize the data in the load, as summarizing the data is QlikView's job. As for the rest, maybe I'm being dense, or am just too lazy to try to follow, but I'm not seeing what you're trying to calculate. What IS 19.85 supposed to represent?

So I decided to just build some raw data myself, and give some basic frequency distribution charts for it including bits and pieces of what was in your post. Maybe something here will be useful. Maybe not.

[#3]

Hi John

Somehow missed your answer so it comes like a christmas present now...

I fully agree that the nice thing about qlik is that we just pull in the facts and let qlik figure out the things we would like to see and in addition are not restricted to any preaggregations hiding details.

I have to show daily punctuality statistics of buses with 5 dimensions where this works fine. I need to do the same now for month's with a scope of 2 years. This will simply make the loading and display too slow without using bins and counters instead of the individual events.

Where I get stuck for weeks now still sounds simple but I can't figure out the way to do it in qlik

What I have are bins and counters (and some dimensions but I should be able to add them later once the calcs are in place)

Using my percentile value as a dimension I will need to get a single value back which represents the binvalue for the percentile. So what I need is the accumulated bincounters in the sequence of the binvalues which I can then relate to the total bincount to base the if statement on

min(if (rangesum(above(bincount,0,rowno()))) / sum(bincount) > Percentile, binvalue))

My problem seams that in order to use above I need to have the binvalue as a dimension which is then ruining my chart. What I did not find is an accumulation function that will allow me to specify the sequence the accumulation should use.

I include my example qvw I am working on. The percentile graph should show the percentiles for each dim-value. The bar chart is showing the values I would need, I finally will also need the boxplot to work.

Thanks for any hint