function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Yaroslav BorisovYaroslav Borisov 

Average date

My accounts have the Date field Activity__c. How can I figure out an average date between all these Activity__c fields?
I tried to use SOQL:

[SELECT AVG(Activity__c) FROM Account WHERE Activity__c != null]

But it shows error:

field Activity__c does not support aggregate operator AVG

VinayVinay (Salesforce Developers) 
Hi Yaroslav,

Date field is not supported for AVG() function.  Hence you see error

Check below references for supported data types and use supported date type in SOQL.

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions_field_types.htm

Hope above information was helpful.

Please mark as Best Answer so that it can help others in the future.

Thanks,
Malika Pathak 9Malika Pathak 9

Hi Yaroslav Borisov,

I want to tell  you that salesforce does not support avg function in SOQL query for  date Field Types in Aggregate Functions



User-added image

But still you want to find the solution then check below code

 

public static void forAvgDate(){
        List<Account> accountList=new List<Account>();
        accountList=[select id,Activity__c from Account where Activity__c!=null];
        
        List<Integer> DatesList=new List<Integer>();
        for(Account ac:accountList){
            String str=string.valueOf(ac.Activity__c);
            String str2=str.replace('-','');
            integer dates=integer.valueOf(str2);
            DatesList.add(dates);
        }
        system.debug(DatesList);
        
        integer total=0;
        for(Integer i=0;i<DatesList.size();i++){
            total=total+DatesList[i];
        }
        system.debug(total);
        Double average=double.valueOf(total/DatesList.size());
        system.debug(average);
    }
Yaroslav BorisovYaroslav Borisov

Thank you very much, guys! I understood that I can't apply the aggregate function to the Date type. But I still can't find an average date )

Malika, your code counts some average Double. Unfortunately, it's not what I need.
Yaroslav BorisovYaroslav Borisov
My solutuion: 
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) {
            Integer currentDiff = Math.abs(d.daysBetween(avgDate));
            Integer savedDiff = Math.abs(closestDate.daysBetween(avgDate));
            if (currentDiff < savedDiff) {
                closestDate = d;
            }
        }
        return closestDate;
    }
    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');
    }