0

My accounts have the Date field Activity__c. How can I figure out an average date between all these Activity__c fields and define the closest date to the average date?

I tried to use SOQL: [SELECT AVG(Activity__c) FROM Account WHERE Activity__c != null]

But it shows an error: field Activity__c does not support aggregate operator AVG.

Now I know that aggregate function average doesn't work with Date. Could somebody help me, please?

2
  • 2
    You can make a formula, which is equal to duration between certain date and Activity__c date in days. Then you can count average of that formula, and calculate which day it is
    – kurunve
    Jan 23, 2021 at 21:24
  • thank you, I did it ) Jan 23, 2021 at 23:36

1 Answer 1

0

I did it:

//method finds closest date to average date

private static Date findClosestDateToAvg(){
    List<Date> dates = new List<Date>();
    for (Account acc : [SELECT Activity__c
                        FROM Account
                        WHERE Activity__c != NULL]) {
        dates.add(acc.Activity__c);
    }
    
    Date avgDate = calcAvgDate();
    Date closestDate = findMinDate();
    for (Date d : dates) {
        if (Math.abs(d.daysBetween(avgDate)) < Math.abs(closestDate.daysBetween(avgDate))) {
            closestDate = d;
        }
    }
    return closestDate;
}

// method calculates average date in fileds Activity__c of Account

private static Date calcAvgDate() {
    Date minDate = findMinDate();
    Date maxDate = findMaxDate();
    Integer diff = minDate.daysBetween(maxDate);
    return minDate.addDays(diff/2);
}

private static Date findMinDate(){
    return (Date)[SELECT MIN(Activity__c)minDate FROM Account][0].get('minDate');
}

private static Date findMaxDate(){
    return (Date)[SELECT MAX(Activity__c)maxDate FROM Account][0].get('maxDate');
}
2
  • well, average is not a middle between max and min date. Consider you have 3 records with january 1, and 1 day with May 31. Will average be march 1 or january 31? Also, if you have more than 50k of records to query, you will hit the limit if you query all the accounts.
    – kurunve
    Jan 24, 2021 at 10:30
  • Thank you for the notice. Probably here is a problem with my English) I heeded to find the closest date to the middle date. Jan 25, 2021 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.