Post

2 followers Follow
0
Avatar

calculating with more than one condition

Dear community 

I'm trying to calculate the sum of numbers from multiple related items. 
We have a project workspace that holds these 4 items: Person, salary, payment periods, projects. 

In the salary app I've got two fields I need in my calculation: Month and percentage. 

Now the challenge, I need to calculate the sum of percentage per month, per person. But since a person can be involved in more than one project there can be more than one salary item per month. I made this calculation but it returns the whole string rather than taking the sum. 

I would also like to make the same calculation where I get the sum of percentage per person, per month, per project. How do I make a calculation with so many conditions? 

Ann-Kathrin Scholtyssek

Please sign in to leave a comment.

7 comments

0
Avatar

Oh I tried to use this function to format the numbers earlier today but didn't get it to work, so right now it's just there.... 

Ann-Kathrin Scholtyssek 0 votes
Comment actions Permalink
0
Avatar

Hi Ann-Kathrin,
some questions: 
1. In which app is this calculation field?
2. What do you mean by "I would also like to make the same calculation where I get the sum of percentage per person, per month, per project."?
3. How are the apps related?

Rainer 

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hi Rainer 

1. the calculation field is in the app "Person". 
2. The percentage is showing with how many percent the person is involved in a project, so I need to know how much percentage the person has in total (across all projects) per month. And I was wondering if I also could get it to show that percentage in total for each project individually? 

3. The salary app has a relationship field to the person that is getting the salary. And it has also a relationshipfield that related the project to the project-app. 


Percentage is a number field in the app "Salary" and month is a calculation field in the same app. 

Thanks in advance for your help! 

Ann-Kathrin Scholtyssek 0 votes
Comment actions Permalink
0
Avatar

Hi Ann-Kathrin,

let's start with the total percentage per month. 
First check if the vars dato and procent have the same length (= same number of items in it)
Under your existing code simply enter:
dato.length, then replace dato with procent (procent.length). Both should return the same number. If not there are empty Dato or Kontrakt% fields in Salary items and you need to replace your @all of-tokens with @all of field with nulls-tokens.

Then in the IFs: instead of += procent[i] 
use += Number(procent[i]) || 0
Also remove the blank spaces between dato and [i]

The function formatNum you must use this way:  "Januar " + numFormat(januar_num) + "%". 
You can also change the function. In the return line add behind .replace()  + "%\n"). The you don't need the % character and "\n"  in the last lines:  
"Januar " + numFormat(januar_num) + "Februar " + numFormat(februar_num) + etc. 

If that calculation is fixed we can go on with the calculation for the projects. 

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hi Rainer 
thank you so much for your help! That worked perfectly. I'm currently trying to get it to work with a timer app to make it current year instead of "2019" only. 

To calculate the percentage project specific... can I add another filter on this calculation? 

Ann-Kathrin Scholtyssek 0 votes
Comment actions Permalink
0
Avatar

For calculating it project specific the code first needs to know to which project this person is related. Are projects and persons directly related? If not you need the to get the projects from the Salary app and must create an array of the projects (where each project is included once only). 
The further structure of the calculation depends on how you want the result: 
January
 - project 1
 - project 2 
February
 - project 2
 - project 3 
 - project 4

or

project 1
 - January
-  February
...
project 2
 - January
-  February
...

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Hi Rainer 

that looks like it's getting a very long code. So we decided not to make it project specific anyways. I'm just wondering if it could be possible to optimise the code so it doesn't depend on a year or month, but just shows the last 5 and the next 5 months from the curent month. 

I figured out with the year, but I don't know how to get the month not to be depending on numbers. Can I filter the results so it does something like if month= current month, show last 5 and next 5 months? 

I hope that makes sense and thank you so much again for your help! 

This is the code I have at the moment:

var procent=@All of procent_til_calc with nulls
var januar_num=0
var februar_num=0
var marts_num=0
var april_num=0
var maj_num=0
var juni_num=0
var juli_num=0
var august_num=0
var september_num=0
var oktober_num=0
var november_num=0
var december_num=0

var curDate=@Max of Date
var curYear=moment(curDate).format("YYYY")


var year=@All of År
var måned=@All of Måned

function formatNum(number){
if (typeof number === "undefined" || number == null || number== "NaN" ) number = 0;
return number.toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, "'")+"%\n"
};


for (var i =0; i < year.length; i++)
{
if (måned[i] == "01" && year [i] == curYear)
{
januar_num +=Number( procent[i]) || 0
}
if (måned[i] == "02" && year [i] == curYear)
{
februar_num +=Number( procent[i]) || 0
}
if (måned[i] == "03" && year [i] == curYear)
{
marts_num+=Number( procent[i]) || 0
}
if (måned[i] == "04" && year [i] == curYear)
{
april_num +=Number( procent[i]) || 0
}
if (måned[i] == "05" && year [i] == curYear)
{
maj_num+= Number( procent[i]) || 0
}
if (måned[i] == "06" && year [i] == curYear)
{
juni_num+=Number( procent[i]) || 0
}
if (måned[i] == "07" && year [i] == curYear)
{
juli_num+=Number( procent[i]) || 0
}
if (måned[i] == "08" && year [i] == curYear)
{
august_num+=Number( procent[i]) || 0
}
if (måned[i] == "09" && year [i] == curYear)
{
september_num+=Number( procent[i]) || 0
}
if (måned[i] == "10" && year [i] == curYear)
{
oktober_num+=Number( procent[i]) || 0
}
if (måned[i] == "11" && year [i] == curYear)
{
november_num+=Number( procent[i]) || 0
}
if (måned[i] == "12" && year [i] == curYear)
{
december_num +=Number( procent[i]) || 0
}
}


"Samlet procenttal af projektallokering for " + @(AUTO) Fornavn + " " + @(AUTO) Efternavn + ":" + "\n" +

"Januar" + " " + formatNum(januar_num)+ "Februar" + " " + formatNum(februar_num) + "Marts" + " " + formatNum(marts_num) + "April" + " " + formatNum(april_num) + "Maj" + " " + formatNum(maj_num) + "Juni" + " " + formatNum(juni_num) + "Juli" + " " + formatNum(juli_num) + "August" + " " + formatNum(august_num) + "September" + " " + formatNum(september_num) + "Oktober" + " " + formatNum(oktober_num) + "November" + " " + formatNum(november_num) + "December" + " " + formatNum(december_num)

Ann-Kathrin Scholtyssek 0 votes
Comment actions Permalink