Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Been in the forum with a similar question before but worked out a more complete example and solution now.
I need to show percentiles in a box plot chart but can not use the raw data (millions and millions of records) but have the values in bins with counters.
For the 5 percentiles required I need to evaluate the bin that has the range of the requested percentile.
The top table shows how I built the formula, unfortunately I found no way to bring these building steps into the graph and had to "expand" the steps into a single large expression (see combined)
In the table "percentile values" I have my formulas that provide the correct value for each dimension and percentile.
Using the same formulas in the boxplot chart the Middle and Bottom expressions work, for Top and the Whiskers it looks like the take the null value (indefinitely high) to draw the line / box against (like from Plot Bottom to null instead of from Plot Bottom to Plot Top).
Any hint?
Juerg
Hi
Agree that it is a bit complex but a bit disappointed that NONE found time to make a suggestion.
Worked further on it and decided, that the nulls in my table should be taken care of.
So I tried to get for each row the min value over a threshold within the range of the dimension. Also syntax allows to do above() with negative numbers to create a below() I finally only succeeded using 2 separate range evaluations, one for the above, the other for the below rows within the dimensions and look for the rangemin of both resultsets.
// use separate evaluation of above and below min value above threshold
rangemin(
below(if(bincume2/max(total <dim2> bincume2) > 0.5,binvalue2),0,$(NoOfBins)+1-rowno() )
, above(if(bincume2/max(total <dim2> bincume2) > 0.5,binvalue2),0, rowno() )
)
So I am happy with this simpler formula which also creates the expected boxplot.
Juerg