You need to sign in to do that
Don't have an account?
Sain
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
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
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
https://help.salesforce.com/HTViewHelpDoc?id=reports_subtotal.htm&language=en_US
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.
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.
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