Calculating average value for a field in a related app

October 16, 2015 17:18

I have two apps 1) survey & 2) employee.

I would like to have a field in my employee app which calculates the average of question #1 for all entries related to that employee from the survey app.

I currently gathering this data with reports, but I would really like to find a way to put this field directly into the employee app under that employee. I searched some forms and it was difficult to find an exact answer so I am sorry if I created a duplicate question here.

Thanks!

Comments

6 comments

Justin VanDervort

October 16, 2015 18:05
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

October 16, 2015 18:22
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

October 17, 2015 10:29
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

October 18, 2015 02:08
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

October 18, 2015 05:24
.... 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

October 18, 2015 10:00
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.

Comments

Didn't find what you were looking for?