+ Start a Discussion
SainSain 

Sum of Amount spend by individual per month

Hi,

I have requirement to display total amount spend by individual per partucular month like shown below, i have Name,Entry Date, Amount__c fields in custom object.  

Name              Current Month Total Amount
Name1                      10000
Name2                        5000
Name3                       40000

Here Current month total Amount is sum of amount__c spend by particular user in whole month.

for example: i have user vinod, he has created 5 records(record1 Amount is 10,record2 Amount is 100,record3 Amount is 10,record4 Amount is 250,record5 Amount is 50) in a particular month.  now i have to display as below

Name                 Current Month Total Amount
vinod                  420(this value is sum of amount spend by particular user for whole month )
ganesh               1000

if any one can provide me a sample code how to achive this it would be great thankfull.

Thanks In Advance
Sain
Best Answer chosen by Sain
SainSain
Hi All,

Thanks for ur support, i got the solution for it by using aggregateResult.

VF page:
<apex:pageBlockTable value="{!cusList}" var="c">
<apex:column value="{!c.name}" headervalue="Name"/>
<apex:column value="{!c.amount}" headervalue="Current Month Amount"/>
</apex:pageBlockTable>

Class:
public String name{get; set;}
public String amount{get; set;}
public CalculationController(string a,string b){
            this.name=a;
            this.amount=b;
            }
public List<CalculationController> cusSumList = new List<CalculationController>();
 public List<CalculationController> getcusList(){
AggregateResult[] AgR =[select Name name, sum(amount__c) amount from object__c where Date__c = This_MONTH group by ROLLUP (Name)];
for (AggregateResult eList : AgR) {
cusSumList.add(new CalculationController(String.valueOf(eList.get('name')), String.valueOf(eList.get('amount'))));
        }
        return cusSumList;
 }

Result:

Name              Current Month Total Amount
Name1                      1000
Name2                        500
Name3                       4000
                                 5500


Regards,
Sain

All Answers

Prabhat Kumar12Prabhat Kumar12
You can easily acihieve this with report. 

https://help.salesforce.com/HTViewHelpDoc?id=reports_subtotal.htm&language=en_US
SainSain
Hi Parabhat,

I have to do some other calculations with this sum values, i think by using report and dashboard we can't perform any other calculation on displayed data.
Abhishek BansalAbhishek Bansal
Hi Sain,

Please try the below code in your trigger :
trigger updateCurrentMonthAmount on yourObject(before insert){
    Set<String> setOfNames = new Set<String>();
    for(yourObject record : trigger.new) {
        setOfNames.add(record.Name);
    }
    List<yourObject> allNewRecordsList = [Select Amount__c,Current_Month_Total__c,EntryDate__c from yourObject where Name IN :setOfNames];
    Map<String,Integer> mapOfNameWithTotalOfCurrentMonth = new Map<String,Integer>();
    
    for(yourObject rec : allNewRecordsList){
        if(rec.EntryDate__c.Month() == Date.today().month()) {
            if(mapOfNameWithTotalOfCurrentMonth.containsKey(rec.Name)){
                mapOfNameWithTotalOfCurrentMonth.get(rec.Name) = mapOfNameWithTotalOfCurrentMonth.get(rec.Name) + rec.Amount__c;
            }
            else {
                mapOfNameWithTotalOfCurrentMonth.put(rec.Name,rec.Amount__c);
            }
        }
    }
    
    for(yourObject record : trigger.new) {
        if(mapOfNameWithTotalOfCurrentMonth.containsKey(record.Name)) {
            record.Current_Month_Total__c = record.Amount__c + mapOfNameWithTotalOfCurrentMonth.get(record.Name);
        }
        else{
            record.Current_Month_Total__c = record.Amount__c;
        }
    }
}

Notes :
1.
Replace yourObject with the API name of your object.
2. This code will run every time when a new record is inserted.

Hopw ths will help you.
 
Thanks,
Abhishek.
SainSain
Hi All,

Thanks for ur support, i got the solution for it by using aggregateResult.

VF page:
<apex:pageBlockTable value="{!cusList}" var="c">
<apex:column value="{!c.name}" headervalue="Name"/>
<apex:column value="{!c.amount}" headervalue="Current Month Amount"/>
</apex:pageBlockTable>

Class:
public String name{get; set;}
public String amount{get; set;}
public CalculationController(string a,string b){
            this.name=a;
            this.amount=b;
            }
public List<CalculationController> cusSumList = new List<CalculationController>();
 public List<CalculationController> getcusList(){
AggregateResult[] AgR =[select Name name, sum(amount__c) amount from object__c where Date__c = This_MONTH group by ROLLUP (Name)];
for (AggregateResult eList : AgR) {
cusSumList.add(new CalculationController(String.valueOf(eList.get('name')), String.valueOf(eList.get('amount'))));
        }
        return cusSumList;
 }

Result:

Name              Current Month Total Amount
Name1                      1000
Name2                        500
Name3                       4000
                                 5500


Regards,
Sain
This was selected as the best answer