SUB ({budget}, {Actual Expenses}) Native - calculated

[JamesCh6]

attempting to: subtract a calculated column of {actual expenses}  from the native project {budget} field. 

example: (column 1 "native budget field") - (column 2 "calculated actuals") = column 3

[KellieGardner]

You would need to include the calculation of your calculated column in the calculation for your third column.

SUB({budget},calculated column expression)

[JamesCh6]

SUB({budget},{DE:Tech Services - Period 1 Actual},{DE:Tech Services - Period 2 Actual},{DE:Tech Services - Period 3 Actual},{DE:Tech Services - Period 4 Actual},{DE:Tech Services - Period 5 Actual},{DE:Tech Services - Period 6 Actual},{DE:Tech Services - Period 7 Actual},{DE:Tech Services - Period 8 Actual},{DE:Tech Services - Period 9 Actual},{DE:Tech Services - Period 10 Actual},{DE:Tech Services - Period 11 Actual},{DE:Tech Services - Period 12 Actual})

not returning any values

[KellieGardner]

Hi James - what is your expression for column 2 (Actual Amount Remaining) that is returning a value? 

[JamesCh6]

Hi, Here are the columns with their expressions:

• Column 1 - Project Budget - {budget} - this is the native field for the overall project budget
• Column 2 - Actual Amount Remaining - SUB({plannedAmount},{DE:Tech Services - Actual Spend})
• Column 3 - Tech Serv PE v PB - SUB({budget},{DE:Tech Services - Period 1 Actual},{DE:Tech Services - Period 2 Actual},{DE:Tech Services - Period 3 Actual},{DE:Tech Services - Period 4 Actual},{DE:Tech Services - Period 5 Actual},{DE:Tech Services - Period 6 Actual},{DE:Tech Services - Period 7 Actual},{DE:Tech Services - Period 8 Actual},{DE:Tech Services - Period 9 Actual},{DE:Tech Services - Period 10 Actual},{DE:Tech Services - Period 11 Actual},{DE:Tech Services - Period 12 Actual})
  
  

I wanted to take the known budget and subtract the calculated value (actual amount) and provide a 3rd column that show an updated budget value.

[KellieGardner]

Have you verified if all your fields for "actual" are setup as number/currency fields and not text fields? 

[JamesCh6]

they are all set to currency

[KellieGardner]

Final Question (I think).

What kind of report are you running? Based on your header for the budget field that's populating (project:budget), I'm now thinking this is not a project report as I expected. If it's not a project report you need to reference the fields differently.

For instance, if it's a project financial data report the project budget is actually {project}.{budget} and not just {budget} and your custom form fields would also need to have the {project} predecessor on them  - {project}.{DE:Tech Services - Period 1 Actual}

[JamesCh6]

okay very helpful. Where can I find the predecessor for an expense object?

Here is the winning formula: 

SUB({project}.{budget},{DE:Tech Services - Actual Spend})

next steps is calculating all of these expenses into a new total that can be viewed in a summary. 

[KellieGardner]

Can you tell me what kind of report you are running?

[JamesCh6]

project report

[KellieGardner]

Hi James -

Since you are running a project report you won't be able to pull in the data from a lower object quite the way you would think. 

• Typically you can report up on objects but not down however there are a few workarounds with things like collections to get task information into a nested column. You could do a collection reference to also get expense information but you wouldn't be able to do the calculations you are trying with it.

• Since your calculated fields are on individual expenses there isn't a true sum of these values stored together for you to reference to add and subtract again. You would first need to find a way to calculated the total sum for each calculated field across your various expenses. 

I would suggest looking into running an expense report to get the data you need instead of a project report. Then you can work with summing the columns, etc. You might even be able to build some calculated fields to get that information to the project and then report it out.