How to subtract non-billable hours in budget calculation
Hi all, I have a calculation in our project app that sums up all costs for the project. In this calculation I would like to subtract costs from timesheets where the category is equal to non-billable. All ideas would be very much appreciated.  Thanks, Pernille

  • Hi Rainer, There're 4 timesheets with the value Nej in Fakturérbar, and one of these with the cost of 1900 DKK - so I really don't understand the result. The timesheets are related to the Project app and the Milestone app. I Have chosen the incoming token from Timesheet via Project relation. No other app has the same field. 
  • Hi Pernille, don't give up too fast :-) Calculation fields are much more flexible and usefull than the reports. It seems that this isn't a problem with the code, but with the app relations.  timeCosts.length = 4 ==>> means: 4 timesheet items are related which have a value in the field "Allokeret" timeCat.length = 0 ==>> means: That no timesheet item as a value in the Category field "Fakturerbar" (no option selected) timeCat.join() = Invalid value null (null): must be string ==>> means the same 2 possible reasons:1. In the related timesheets the users haven't selected an option in the field "Fakturerbar"2. You have picked the wrong token for that field "Fakturerbar". Do you have in the project app a relationship field which points to the timesheet app? If so, you would have 2 tokens for "Fakturerbar" - one via an incoming relation, one via an outgoing relation. You have to pick the right one. Or is there another app which references the Projects App and also has a field "Fakturerbar"? Rainerrg@delos-consulting.com
  • Unfortunately I get some different results: timeCosts.length = 4 timeCat.length = 0 timeCat.join() = Invalid value null (null): must be string nonBillCosts = 0   Maybe I should just leave this and use a report instead. 
  • In Udregning is the result of totalCosts - nonBillCosts. And cause the same amount is in teh TotalCosts field it seems, nonBillCosts are calculated as 0. So let's check if all variables are correct. Please do the followingAt the end of the calculation as last line enter:timeCosts.length (must be 2)delete it and entertimeCat.length (must be 2)delete it and entertimeCat.join() (must be "Nej,Nej")delete it and enternonBillCosts (must be the sum of the fields Allokeret in the 2 related timesheets). 
  • The results are in screenshot 2. (Udregning = 1900,00) This calculation is related to 2 timesheets with Fakturérbar = Nej. It's a single choice cat. field - Yes or No. 
  • The calculation and the script seem to be ok.  nonBillCosts seem to be 0. (enter nonBillCosts as last line - what is shown as result?) Is  "Fakturerbar" a single choice or a multi choice category field? Are there timesheets related with Fakturerbar == "Nej". Rainer
  • Hi Pernille, please share a screenshot of your calculation field. Does an error notifiaction appear in the preview? Rainer
  • Hi Rainer, I've tried to add the code, but it doesn't seem to subtract. In my project app I have these calculations: 1: Allocated costs = @Sum of Allocated + @Sum of Expenses 2: Non-allocated budget = @Budget - @Allocated costs How can I combine your calculation?  Thanks, Pernille
  • Hi Pernille, this should do it:  var totalCosts = @Sum of whatever field you use for costs;var timeCosts = @All of field in timesheet where the costs are in;var timeCat = @All of Category field wher "non-billable is an option;var nonBillCosts = 0;for(var i = 0; i < timeCostslength;i++){if(timeCat[i] == "non-billable"){nonBillCosts +=timeCosts[i];}};totalCosts - nonBillCosts Hope that helps,Rainer
This question is closed.