0

I want to show a chart which shows revenu trend of trailing 12 months. In my dataset, I have Month and Year in String format i.e. month has values like "1", "2" etc and year has values like "2013", 2014" etc. Due to this, sorting of data series is difficult. I have been able to sort data at month level, but not able to incorporate sorting of by year. Following is my controller code.

public List<OpportunityData> getOpportunityData() {
Integer regnInt = (regn!=null) ? Integer.valueOf(regn) : -1;  
AggregateResult[] result = [SELECT Month__c, Year__c, Sum(Total_Rental_Revenue_Actual__c) Reve, Sum(Rental_Revenue_Goal__c) Gol FROM User_Stats__C 
    where ((Month__c = : string.valueof(today.month()) AND Year__c = : string.valueof(today.year()))
    OR (Month__c = : string.valueof(monthPlus1.month()) AND Year__c = : string.valueof(monthPlus1.year()))
    OR (Month__c = : string.valueof(monthPlus2.month()) AND Year__c = : string.valueof(monthPlus2.year()))
    OR (Month__c = : string.valueof(monthMinus1.month()) AND Year__c = : string.valueof(monthMinus1.year()))
    OR (Month__c = : string.valueof(monthMinus2.month()) AND Year__c = : string.valueof(monthMinus2.year()))
    OR (Month__c = : string.valueof(monthMinus3.month()) AND Year__c = : string.valueof(monthMinus3.year()))
    OR (Month__c = : string.valueof(monthMinus4.month()) AND Year__c = : string.valueof(monthMinus4.year()))
    OR (Month__c = : string.valueof(monthMinus5.month()) AND Year__c = : string.valueof(monthMinus5.year()))
    OR (Month__c = : string.valueof(monthMinus6.month()) AND Year__c = : string.valueof(monthMinus6.year()))
    OR (Month__c = : string.valueof(monthMinus7.month()) AND Year__c = : string.valueof(monthMinus7.year()))
    OR (Month__c = : string.valueof(monthMinus8.month()) AND Year__c = : string.valueof(monthMinus8.year()))
    OR (Month__c = : string.valueof(monthMinus9.month()) AND Year__c = : string.valueof(monthMinus9.year()))) 
and Region__c =: regnInt and District__c =: dist and Sales_Rep__c != null and Rental_Revenue_Goal__c != null and Rental_Revenue_Goal__c != 0 GROUP BY Year__c, Month__c ORDER BY Year__c asc, Month__c asc];

List<OpportunityData> oppsData = new List<OpportunityData>();
for (AggregateResult a : result)
{
    OpportunityData opp = new OpportunityData((String)a.get('Month__c'),
                                              (String)a.get('Year__c'),
                                              (Double)a.get('Reve'),
                                              (Double)a.get('Gol'));    
    oppsData.add(opp);
}
oppsData.sort();
return oppsData;
}

public class OpportunityData implements Comparable

{
public Integer compareTo(Object o) {
    return month-((OpportunityData)o).month;
}
public Integer month { get; set; }
public Integer year { get; set; }    
public String monthString { get; set; }    
public Double Revenue { get; set; }
public Double Goal { get; set; }
public Double Attnmnt { get; set; }        
public OpportunityData(String mon,String yr, Double Reve, Double Gol)
{
    month = integer.valueof(mon);
    year = integer.valueof(yr);        
    monthString = DateTime.newInstanceGMT(System.Date.newInstance(2000, month, 1), System.Time.newInstance(0, 0, 0, 0)).formatGMT('MMM');        
    Revenue = Reve/1000;
    Goal = Gol/1000;
    Attnmnt = Reve / Gol * 100;            

}
}
1

Change your wrapper class to this:

public class OpportunityData implements Comparable

{
public Integer compareTo(Object o) {
    return revenueDate.getLong()-((OpportunityData)o).revenueDate.getLong();
}
public Integer month { get; set; }
public Integer year { get; set; }    
public String monthString { get; set; }    
public DateTime revenueDate { get; set; }
public Double Revenue { get; set; }
public Double Goal { get; set; }
public Double Attnmnt { get; set; }        
public OpportunityData(String mon,String yr, Double Reve, Double Gol)
{
    month = integer.valueof(mon);
    year = integer.valueof(yr);        
    revenueDate = DateTime.newInstance(System.Date.newInstance(year, month, 1), Time.newInstance(0, 0, 0, 0));
    monthString = revenueDate.format('MMM');
    Revenue = Reve/1000;
    Goal = Gol/1000;
    Attnmnt = Reve / Gol * 100;            

}
}

This gives you the ability to incorporate the year and month into the calculation.

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.