How to customize ojet Chart boxplot sort by median

User_RPM7N

Hello,

is there a way (except via SQL) to sort a boxplot by median or other values ?

actually we do this by SQL like this:

1)we calculate the median in sql with the MEDIAN function

2)we add spaces before the labels with lpad.......

with basis as(

    select lbl, val from(

    select  'world' lbl,  round(DBMS_RANDOM.value*100) as val  from dual connect by level< 1000)

    union all 

    select  'hello' lbl,  round(DBMS_RANDOM.value*75) as val  from dual connect by level< 100)

select sorthelp.lbl, b.val 

FROM

    basis b , 

    (

    select  

            med, 

            lpAd(' ',row_number () over (order by med  ,' '))|| lbl as lbl

        from 

            (select distinct median(val) over(partition by lbl ) med,lbl

            from basis)

      )sorthelp

    where trim(b.lbl) = trim(sorthelp.lbl)

====================================

this solution is not really satisfying, because of the complexity of our SQLs

Does anybody know another way?

1) perhaps inside the Advanced JavaScript Initialization Code🙄

OR

perhaps after refresh ?🙄

(a simple call like this can't be the solution, because a boxplot has several values:

$("#our_chart_jet").ojChart({sorting:'descending'});)

========================

MANY THANKS 😊

Oleh Tyshchenko

Try this

function( options ){
    // Setup a callback function which gets called when data is retrieved, it allows to manipulate the series
    options.dataFilter = function( data ) {
        for (var i=0;i<data.series.length;i++) {
                console.log(data);
                // resort the series items by a median value (q2)
                data.series[i].items.sort(function(a,b){
                    if(a.q2< b.q2) return -1;
                    if(a.q2 >b.q2) return 1;
                    return 0;
                });
                // sync labels according to a new items order
                data.groups = [];
                for (var j=0;j<data.series[i].items.length;j++) {
                    data.groups.push({name:data.series[i].items[j].name});
                }
        }
        
        return data;
    };
    
    return options;
}

This will work correctly for a single series case only I believe. So this maybe not the best advice. Use carefully.

Oleh Tyshchenko

Try to resort the data prepared for you by APEX in the Advanced JavaScript Initialization Code:

function( options ){
    // Setup a callback function which gets called when data is retrieved, it allows to manipulate the series
    options.dataFilter = function( data ) {
        for (var i=0;i<data.series.length;i++) {
                // resort the series items by a median value (q2)
                data.series[i].items.sort(function(a,b){
                    if(a.q2< b.q2) return -1;
                    if(a.q2 >b.q2) return 1;
                    return 0;
                });
        }
        return data;
    };
    
    return options;
}

User_RPM7N

Hallo Ole,😀

Thank you very much for this solution!

This works fine to sort the boxplots-VALUES in the chart⭐️,

but the LABELS are not sorted accordingly.... they keep their alphabethic order🙄

HOW to sort the Labels accordingly?

greetings from Munich 😀

Oleh Tyshchenko

Try this

function( options ){
    // Setup a callback function which gets called when data is retrieved, it allows to manipulate the series
    options.dataFilter = function( data ) {
        for (var i=0;i<data.series.length;i++) {
                console.log(data);
                // resort the series items by a median value (q2)
                data.series[i].items.sort(function(a,b){
                    if(a.q2< b.q2) return -1;
                    if(a.q2 >b.q2) return 1;
                    return 0;
                });
                // sync labels according to a new items order
                data.groups = [];
                for (var j=0;j<data.series[i].items.length;j++) {
                    data.groups.push({name:data.series[i].items[j].name});
                }
        }
        
        return data;
    };
    
    return options;
}

This will work correctly for a single series case only I believe. So this maybe not the best advice. Use carefully.

User_RPM7N

Hello Ole,😀

⭐️⭐️you are the very best!!!⭐️

Many thanks for your perfect solution!

⭐️⭐️⭐️⭐️Great!, It works soooooo fine 😀⭐️⭐️⭐️

greetings from Munich 😀