Post

2 followers Follow
0
Avatar

Calculation - Sum IF based on category

Hello - I have a membership app that has a referenced app for status changes (Active to Inactive etc.). I've created a field in the status change app to calculate the amount of days in the status (between two date fields).

I'd like to create a calculation field in the membership app that calculates totals of the "days in status" calculation based on their category and per member. End result would be a calculation field that shows me how many total days were spend in an active status or inactive status per member.

RJ Best

Please sign in to leave a comment.

1 comment

0
Avatar

Hi, 

you can create either one calculation field for each status (result = total number of days in this status) or a list like: 
Active: 10 days
Inactive: 5 days

Option one field for each status: 

var status = @All of Status;
var days = @All of Days in Status;
var sum = 0;
for(var i = 0; i < status.length; i++);
      if(status[i] == "Active"){
         sum += Number(days[i]) || 0;
     }
};
sum

For other status categories just change the IF, e.g status[i] == "Inactive"

Option list:

var status = @All of Status;
var days = @All of Days in Status;
var statusArray = ["Active","Inactive","Whatever status"]
var list = [];
for(var j = 0; j < statusArray.length; j++){
      var sum = 0;
      for(var i = 0; i < status.length; i++);
            if(status[i] == statusArray[j]){
              sum += Number(days[i]) || 0;
           }
      }
list.push(statusArr[j] + ": " + sum){
};
list.join("\n")

In the var statusArray enter all status categories you want to check.

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink