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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .