Enhancement Idea -- Median Value Rollup

[benchastain]

I was told by a colleague who sat in on a webinar today that I should add this as an enhancement idea:

The idea is to create a DLRS formula/function (like the Average or Count) for median value because there is no native functionality in Salesforce to rollup the median value in a set of related/child records.

The ability to rollup a median value of child/related records has been requested dozens of times over the years, but it is not a priority for Salesforce to add it (probably rightly so).

At present, an admin/developer has to write an Apex trigger to calculate the median values. I decided to take on doing this about 3 months ago in order to teach myself more about writing triggers, but most admins (particularly those at nonprofits) don't necessarily have time or resources to learn to write an Apex trigger and test coverage.

With the DLRS it seems like you have the opportunity to create a quickly editable/deployable "Median Value Rollup".

If it would help your process, I can supply you with the Apex I wrote to accomplish the calculation for a rollup of Opportunities to the Contact record.

[afawcett]

Yep thanks for this and yes any code snippets would be useful for sure! 👍

[benchastain]

You'll have to forgive the coding and conventions. I'm pretty new to that aspect of the Salesforce world and currently don't have very much occasion for collaboration/input when it comes to writing Apex.

The class I created (called OpportunityTriggerHandler) looks like this:

public class OpportunityTriggerHandler {
    public void onBeforeInsert(list <Opportunity> newOppList, map <id, Opportunity> newOppMap){

    }
//Trigger calculateMedian after insert    
    public void OnAfterInsert(list <Opportunity> newOpps, Map<ID, Opportunity> newOppsMap){
        calculateMedian(newOpps);
    }

    public void OnBeforeUpdate(list <Opportunity> oldOpps, list <Opportunity> newOpps, Map<ID, Opportunity> oldOppsMap, Map<ID, Opportunity> newOppsMap){

    }
 //Trigger calculateMedian after update   
    public void OnAfterUpdate(list <Opportunity> newOpps, Map<ID, Opportunity> oldOppsMap){
        calculateMedian(newOpps);
    }

    public void OnBeforeDelete(list <Opportunity> deletedOpps, Map<ID, Opportunity> oldOppsMap){

    }
 //Trigger calculateMedian after delete   
    public void OnAfterDelete(list <Opportunity> deletedOpps, Map<ID, Opportunity> oldOppsMap){
        calculateMedian(deletedOpps);
    }

//Method for calculating median
//Create create a list of opportunities related to a contact
    public void calculateMedian(list <Opportunity> newOpps){
        set <id> conIdSet = new set<id>();
//Create a subset of that list of opportunities that is closed won                 
        for(Opportunity newOpp: newOpps){
            if(newOpp.IsWon && newOpp.npsp__Primary_Contact__c != null){ 
                conIdSet.add(newOpp.npsp__Primary_Contact__c);
                            }
        }

        list <Id> conIds = new list <id>();
        conIds.addall(conIdset);

        list <Contact> cons = [select id, accountid

                         from contact where 
                           id in :conIds
                               ];
      //Declares variable and sets value of sizeOflist, index, and median
                        Integer sizeOfList = 0;
                        Integer index = 0;
                        Decimal median = 0.0; 

        if(!cons.isempty()){  
            for (contact c: cons){
    //arranges opportunities in descending order by amount                
                list <Opportunity> oppList = [select Amount, ID from Opportunity 
                      where isWon = True AND RecordTypeId = '012A0000000eJnT' AND npsp__Primary_Contact__c = :c.ID order by Amount desc];                
    //caculates size of the list, indexes values            
                if(!OppList.isempty()){
                        sizeOfList = oppList.size();
                        index = sizeOfList - 1;
                        median = 0.0; 
      //if there is no remainder when the size of the list is divided by two, then take the average of the two middle 'amount' values
      //if there is a remainder when the list size is divided by two then take half the index value to determinde the middle 'amount' value                  
                        if (Math.mod(sizeOfList, 2) == 0) {
                        median = (oppList[(index-1)/2].Amount + oppList[(index/2)+1].amount)/2;
                        }else{
                        median = oppList[(index+1)/2].Amount;
                        }
                        system.debug('the median is: '+median);
                        c.Median_Contact_Donation_Amount__c = median;
                    }
            }
            Update cons;
        }

    }

}

I'm sure you don't need this snippet, since I'd imagine the DLRS must create its own trigger based on the object selected. But the trigger I wrote is:

 trigger OpportunityTrigger on Opportunity (after delete, after update, after insert) {
    OpportunityTriggerHandler handler = new OpportunityTriggerHandler();

    if(Trigger.isDelete && Trigger.isAfter){
      handler.OnAfterDelete(Trigger.old,Trigger.oldMap);  
    }
    if(Trigger.isUpdate && Trigger.isAfter){
      handler.OnAfterUpdate(Trigger.new,Trigger.oldMap);  
    }
    if(Trigger.isInsert && Trigger.isAfter){
      handler.OnAfterInsert(Trigger.new,Trigger.oldMap);  
    }

}

Hope it's of some help!

Like so many others, I really dig the DLRS app.

[benchastain]

//arranges opportunities in descending order by amount                
                list <Opportunity> oppList = [select Amount, ID from Opportunity 
                      where isWon = True AND RecordTypeId = '012A0000000eJnT' AND npsp__Primary_Contact__c = :c.ID order by Amount desc];

I just realized that this trigger was also built to filter out and calculate for only one of our record types which is called a Contact Donation--that's probably the only piece that needed any real elaboration. Everything else is probably fairly straight forward.

[afawcett]

Thanks @benchastain for sharing!

[fajilGH]

Hi this Fajil plz let me know about how to create roll up summary filled via look up only i red ur article but i couldn't understand bekoz i new to sfdc @ fajil143@gmail.com

[afawcett]

@fajilGH please review the Documentation on the README file.

[dylanof]

Median value rollups would be amazing, great idea! It baffles me that Salesforce doesn't support medians at all.