Sum up items in a calculated field

Comments

11 comments

  • Kent Watson

    Try This Fady

     

    note that for each entry in @all of organisation there needs to be an entry in @all of cost per participant even if it is $0

     

    var oraniz = @All of Organisation;
    var organizsort = organiz.sort();  //sort organiz to help get unique
    var cost = @All of Cost Per Participant;
    var txt = "";
    var unique = [];  //create new array of unique organisations
    var summary = "";
    for (var i = 0; i < oraniz.length; i++){  //for loop to create new unique array
        if (organizsort[i] != txt){
        txt = organizsort[i];
        unique.push(txt);  //create new array of unique organisations
    }};

    for (var j = 0; j < unique.length; j++){  //loop for each unique organisation
        var sum = 0;
        for (var i = 0; i < oraniz.length; i++){  //loop to compare unique organisation against all organisations
            if (oraniz[i] == unique[j]){
            sum += cost[i]  //when match is found add the cost
        }};
        summary += unique[j] + " = $" + sum + "\n";  //compile output with each unique company on a new line
    };
    summary

    0
    Comment actions Permalink
  • Kent Watson

    Try This Fady

     

    note that for each entry in @all of organisation there needs to be an entry in @all of cost per participant even if it is $0

     

    var organiz = @All of Organisation;
    var organizsort = organiz.sort();  //sort organiz to help get unique
    var cost = @All of Cost Per Participant;
    var txt = "";
    var unique = [];  //create new array of unique organisations
    var summary = "";
    for (var i = 0; i < organiz.length; i++){  //for loop to create new unique array
        if (organizsort[i] != txt){
        txt = organizsort[i];
        unique.push(txt);  //create new array of unique organisations
    }};

    for (var j = 0; j < unique.length; j++){  //loop for each unique organisation
        var sum = 0;
        for (var i = 0; i < organiz.length; i++){  //loop to compare unique organisation against all organisations
            if (organiz[i] == unique[j]){
            sum += cost[i]  //when match is found add the cost
        }};
        summary += unique[j] + " = $" + sum + "\n";  //compile output with each unique company on a new line
    };
    summary

    0
    Comment actions Permalink
  • Fady kreidy

    Thank you very much Kent!

    I think we are almost there. However when i tried your suggestion, I received the below:

    Company A= $NaN
    Company B = $NaN
    Company C = $NaN
    Company D = $NaN
    Company E = $NaN

    What does it mean?

    0
    Comment actions Permalink
  • Kent Watson

    I think it means that the cost value is empty.  can you please check by doing the following

    on the last line comment out summary e.g.

    //summary

    then add a new line

    @Sum of Cost Per Participant 

    and tell me what the output is

    0
    Comment actions Permalink
  • Fady kreidy

    wow.. it worked after i put "sum += cost" instead of "sum += cost[i]"

    Strange, no?

    0
    Comment actions Permalink
  • Kent Watson

    Indeed that is Strange.

    Are you confident the data is accurate?

    Can you send me a screen capture of your code

    0
    Comment actions Permalink
  • Fady kreidy

    Yes, the data is accurate. I tried it on different records with different inputs

    I put @Sum of Cost Per Participant but podio did it find it

    Here is the screen capture

    0
    Comment actions Permalink
  • Kent Watson

    Ah OK I get why you don't need cost[i] in your formula. It is because cost is stored in the current app not the related app

    I can tell this because you called up var cost = @Cost per Participant which is coming from the home app

    if you were to call it up from var cost = @All of Cost per Participant it would be coming from the related app and hence an array

    This also explains why the @Sum of Cost per Participant didn't work. this will only work coming from a related app

    basically information from related apps is stored as a javascript array and the cost[i] calls the value from position "i" in the array.  Since the information is stored in the home app there is no array and happy days.  With this in mind you could probably make the formula simpler, but if it works happy days :)

    click here to learn about arrays

    0
    Comment actions Permalink
  • Fady kreidy

    Thanks a lot Kent. I am not an advanced Java programmer but i know few stuff. It always feels good when you learn something you, especially from an expert like you.

    Yes... it is working :)

    0
    Comment actions Permalink
  • Melon Wedick

    Similar situation but adapting the code above is returning incorrect values for me. I have a payroll period app that is linked to timesheets based on date, and would like to show on the pay period record a summary of the number of hours worked (from timesheet app field "Hours Worked") per employee (from timesheet app field "Employee") for that period. 

    I tried this, based on the above solution, but the calculated numbers end up wrong in unpredictable ways (some over, some under). The total # hours ends up correct, but the totals per employee are wrong. Thoughts on where I'm going wrong?

    var emp = @All of Employee;
    var empsort = emp.sort();
    var hours = @All of Hours Worked;
    var txt = "";
    var unique = [];
    var summary = "";
    for (var i = 0; i < emp.length; i++){
    if (empsort[i] != txt){
    txt = empsort[i];
    unique.push(txt);
    }};

    for (var j = 0; j < unique.length; j++){
    var sum = 0;
    for (var i = 0; i < emp.length; i++){
    if (emp[i] == unique[j]){
    sum += hours[i]
    }};
    summary += unique[j] + " - " + sum + " hours "+ "\n";
    };
    summary

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hello Melon, 
    your uniques are not unique. 

    var emp = @All of Employee;
    var empsort = emp.sort();
    var hours = @All of Hours Worked;
    var unique = empsort.filter(function(item, pos) {
    return empsort.indexOf(item) == pos;
    });
    var rows = [];
    var totalHrs = 0;
    for (var j = 0; j < unique.length; j++){
           var empHrs = 0;
           for (var i = 0; i < emp.length; i++){
                 if (emp[i] == unique[j]){
                    empHrs += hours[i] || 0;
                }
         };
    totalHrs += empHrs;
    rows.push(unique[j] + " - " + empHrs + " hours");
    };
    var totalRow = "\n**TOTAL - " + totalHrs + "hours**";
    rows.join("\n") + totalRow

    Rainer 

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk