Forum Stats

  • 3,837,812 Users
  • 2,262,300 Discussions
  • 7,900,398 Comments

Discussions

How to customize ojet Chart boxplot sort by median

User_RPM7N
User_RPM7N Member Posts: 7 Green Ribbon
edited Mar 10, 2021 3:28PM in APEX Discussions

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 😊

Best Answer

  • Oleh Tyshchenko
    Oleh Tyshchenko Member Posts: 716 Gold Trophy

    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.

Answers

  • Oleh Tyshchenko
    Oleh Tyshchenko Member Posts: 716 Gold Trophy

    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
    User_RPM7N Member Posts: 7 Green Ribbon

    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
    Oleh Tyshchenko Member Posts: 716 Gold Trophy

    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
    User_RPM7N Member Posts: 7 Green Ribbon

    Hello Ole,😀

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

    Many thanks for your perfect solution!

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

    greetings from Munich 😀