Calculating average value for a field in a related app

Comments

6 comments

  • Justin VanDervort

    UPDATE: silly me.. I figured out what I was doing wrong. I was using a category field for the customers to enter 1-6. I forgot, since they aren't really numbers and they are categories, the caluculation options do not show up.

    Question: Are there any work arounds for customers to 'select' a number in a series opposed to 'typing' a number so that i can keep the data uniform and simple for the customer to input?

    1
    Comment actions Permalink
  • Rainer Grabowski

    Hi Justin,

    you can keep the category field for the customer, but you have to use a more sophisticated code in your calculation field. You have to parse the string to a number and you have to count the related items:

     var cat = @All of question #1
    
     catSum = 0
     for(var i = 0; i < cat.length; i++){
     catSum +=parseFloat(cat[i])/cat.length
     };
     catSum || 1
    

    Rainer
    rg@delos-consulting.com

    1
    Comment actions Permalink
  • Justin VanDervort

    I appreciate your answer. I would love to better understand how to utilize it. I only know basic fundamentals about programing so I am very unsure how to use your answer correctly. Could you help me with that?

    0
    Comment actions Permalink
  • Justin VanDervort

    UPDATE!!

    Hooray! Thank you so much.. after a lot of messing around, this coding worked great! I just had to figure out all of the extra nuances associated with using it. I can't thank you enough!

    0
    Comment actions Permalink
  • Justin VanDervort

    .... Is there a way to modify this so that it only pulls from the last 30 days of data?

    0
    Comment actions Permalink
  • Rainer Grabowski

    That's a bit tricky, cause in your calculation field you need a value "Today" - and that is not really possible. The reason: A calculation will only be updated if one variable changes. An example:
    The function moment() gives back the "moment" a change happens in the calculation. For your use case it would mean: every time a new Survey is referenced to an employee, moment changes. So if today a survey is added, moment()shows Oct. 18th, 2015. But this date will stay till the next survey is added. If tomorrow no item will be added, you'll still have Oct., 18th, 2015 as today value. There are some workarounds for that, but they are not ideal (you can search for "current date" or "current day" here in the community and you'll find some more information about this problem).

    So if each employee item is updated dalily you can do a calculation "pull from the last 30 days of data". If not you can filter for: last added survey item minus 30 days . But that could mean that the time span for each employee is different.

    Anyway, you need a date field in the survey app. If you don't have one, create a calculation field in the Survey app which gets the creation date of the survey item:

    @created on

    Then you can filter: Get survey items only, if they are max 30 days older than the last added survey item.

     var cat = @All of question #1;
     var date = @All of date field;
     var lastDate = @Max of date field;
    
     var items30Days = 0
     for(var i = 0; i < cat.length; i++){
     if(date[i] <= lastDate && date[i] >= moment(lastDate).subtract(30,"d").toDate() ){
     ++items30Days ;
     }}
    
     catSum = 0
     for(var i = 0; i < cat.length; i++){
     if(date[i] <= lastDate && date[i] >= moment(lastDate).subtract(30,"d").toDate() ){
     catSum +=parseFloat(cat[i])/items30Days
     }};
     catSum 
    

    The first part "var items30Days" looks through all items with a so called "for-loop" , collects all items with a date between the date of the last added item (<=) and last added item date minus 30 days (>=). Then it counts the items (+=). moment()functions are necessary for manipulating or calculating with dates (s. http://momentjs.com/docs/ )

    The second part "catSum" loops through all items, gets those with dates in the wanted time span, sums their (parsed) category values and divides the sum by the result of var items30Days. Btw: The vars you can name like you want (but no numeral or special character except $ or _ allowed at the beginning).

    Rainer
    rg@delos-consulting.com

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk