Expand my Community achievements bar.

Come join us for our Coffee Break this WEDNESDAY on top takeaways from Adobe Summit!

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

Avatar

Level 2

JamesCh6_2-1707257140685.png

 

 

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

 

11 Replies

Avatar

Community Advisor

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

 

SUB({budget},calculated column expression)

 

 

 




Avatar

Level 2
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

Avatar

Community Advisor

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




Avatar

Level 2

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.

Avatar

Community Advisor

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

 

KellieGardner_0-1707417451671.png

 




Avatar

Community Advisor

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}

 

 




Avatar

Level 2

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. 
 

Avatar

Community Advisor

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




Avatar

Community Advisor

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.

KellieGardner_1-1707838218549.png

 

  • 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.