1

The code below is working so far.

Now I added two fields to the visualforce page (selected_year_1 selected_year_2) where the user can select a year (2016/2017/2018...) for both fields. This years should loop thru my code below. At the end I should have 24 values. I tried a bit with the following code, but I don't know to keep the values, because the years loop thru but overwrite my values from the first loop.

Integer[] arrayOfYear = new List<Integer>();

//Adding elements in Array
arrayOfYear.add(Selected_Year_1);
arrayOfYear.add(Selected_Year_2);


for (Integer j = 0; j<arrayOfYear.size(); j++) {
   //This loop will print all the elements in array
   system.debug('Values In Array: '+arrayOfYear[j]);
} 

How can I keep all values in seperate variables?

public void getData(){   
    
    String SelectClause = 'SELECT BWS__c, Date__c FROM Sales__c';
    
    List <String> WhereClauses = new List <String>();   
    
    IF (SelectedUserId != ('North') && SelectedUserId != ('South') && SelectedUserId != ('East') && SelectedUserId != ('West') && SelectedUserId != Null) {
        WhereClauses.Add('Salesman__r.Id = \'' + SelectedUserId + '\'');
    }
    IF (SelectedUserId == ('')) {
        WhereClauses.Add('');
    }    
 
    WhereClauses.Add('BWS__c != NULL AND Calendar_Year(Date__c) = ' + Selected_Year_1); 
    String WhereClause = ' WHERE ' + String.join(WhereClauses, ' AND ');  
    String SQL = SelectClause + WhereClause;
    
    List <Sales__c> SalesList = Database.query(SQL);

    Decimal[] values = new Decimal[12];
    
    FOR(Integer i = 0; i < values.size(); i++) 
        { values[i] = 0; }
           
            FOR(Sales__c Sales : SalesList)
                {        
                    Integer Mo = Sales.Date__c.month();
                    values[Mo-1] += Sales.BWS__c;
                }
                               
        Y1_01_BWS = values[0];
        Y1_02_BWS = values[1];
        Y1_03_BWS = values[2];
        Y1_04_BWS = values[3];
        Y1_05_BWS = values[4];
        Y1_06_BWS = values[5];
        Y1_07_BWS = values[6];
        Y1_08_BWS = values[7];
        Y1_09_BWS = values[8];
        Y1_10_BWS = values[9];
        Y1_11_BWS = values[10];
        Y1_12_BWS = values[11];          

}

EDIT

I found this snippet, but it's the same problem. I have specific values. I thoght that I run the aggregate and transfer the values to the google line chart. But how can I the both selected years with the 12 values of month.

 List<AggregateResult> aggList =[Select Attendence_Status__c,Count(Id) FROM Attendance__c WHERE Enrollment__r.Student__c=:contactId GROUP BY Attendence_Status__c];
    for(AggregateResult aggResult:aggList){
        if(aggResult.get('Attendence_Status__c')=='Partially Attended'){
            partialCount= (Integer)aggResult.get('expr0');
        }else if(aggResult.get('Attendence_Status__c')=='Attended'){
            attendedCount = (Integer)aggResult.get('expr0');
        }else if(aggResult.get('Attendence_Status__c')=='Absent'){
            absentCount= (Integer)aggResult.get('expr0');
        }
2

The short answer is that this sounds like something you should be building a report for, rather than trying to solve the problem with Apex/Visualforce.

However, if that's not an option or you're doing this as a project to learn apex/visualforce, then the simple answer is pretty much the same as your question's title (at time of writing). You should use a nested data structure (and possibly a nested loop).

Having individual variables (like Y1_01_BWS, Y1_02_BWS, etc...) isn't an approach I'd recommend. Besides taking more than a few lines, it also locks you in to having precisely that much data. Only have 6 months' worth of data to report, you still have 12 variables. Need to report on more than one year of sales? You either need to declare more variables, or pick a single year that you want to sum up.

Instead, making use of collections (Lists, Sets, and Maps) is the preferred approach because they can basically hold any amount of data. Need to add another piece of data? Just .add() it to your list!

Given the data you seem to be working with, I think a Map<Integer, List<Decimal>> would make the most sense. They key of the map would be the calendar year of your Date__c field, and each "year" would hold its own List<Decimal> to track your number for each individual month.

I think that approach is preferable to something like, say, a List<List<Decimal>> or even just a flat List<Decimal> because your query isn't ordering rows by date. It is possible to use one of those two collections for this, but it's more work than using a Map (because you couldn't assume that rows are returned in ascending order of Date__c).

Some example code (that assumes you only have a single sale per month)

Map<Integer, List<Decimal>> yearToMonthySalesMap = new Map<Integer, List<Decimal>>();

for(Sales__c sale :SalesList){
    Integer year = sale.Date__c.year();
    Integer month = sale.Date__c.month();

    // With maps, you'll generally find yourself needing to check if the key exists
    //   before trying to .get() (otherwise you risk running into Null Pointer Exceptions)
    // This pattern, where we check for existence first, then add the key if it doesn't
    //   , is a well-established approach
    if(!yearToMonthySalesMap.containsKey(year)){
        // Still declaring the list to have 12 elements, because it'll allow us to
        //   easily add an entry for any given month in any given order
        yearToMonthySalesMap.put(year, new List<Decimal>(12));
    }

    // At this point, we're guaranteed to have _something_ in our map for our key value
    //   so it's safe to .get(year) without any more checks.
    List<Decimal> yearlyList = yearToMonthySalesMap.get(year);

    // Non-primitive types are stored in collections as _references_, meaning that if
    //   we grab the data from the map, and update it outside of the map, it automatically
    //   gets updated inside the map too (unless you make a clone to break the reference).

    // Also, we're using the 2-parameter version of the .add() method on the List class.
    // It allows us to specify the index we want to insert the data into
    // Array-style indexing a la myList[index] = value would also work
    yearlyList.add(month - 1, sale.My_Field__c);
}

Bonus

If you have multiple Sales__c records for a given month, it could be worth exploring the option of having SOQL do the heavy lifting for you.

Instead of keeping running totals in Apex, you can just use a query that uses the GROUP BY keyword so you can use aggregate functions like COUNT() and SUM().

Something like the following would be helpful

[SELECT CALENDAR_YEAR(CreatedDate), CALENDAR_MONTH(CreatedDate), SUM(Sale_Total__c) totalSales 
FROM Sales__c 
GROUP BY CALENDAR_YEAR(CreatedDate), CALENDAR_MONTH(CreatedDate)]

Such a query wouldn't return a List<Sales__c>, but rather a List<AggregateResult>. I'll leave you to do more reading on that topic if you're interested.

1
  • Thanks for your realy good response. Aggregate looking good, but I don't know how can I transfer the values of the aggregate to the google charts. I googled a lot, but found nothing about this. I found the snippet above mentioned, but here was also requested special values. – Sascha Jul 22 '20 at 5:45

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.