Need calculated field for delta between planned expense amount and actual expense amount

[LindenRh]

I have been futsing about with a calculated custom field and I am stuck... I want to create either a calculated field within an expense custom form, or a calculated column in the report that subracts the actual expense amount from the planned expense amount and returns the value. The expense is embedded within tasks, and the report I have created is an expense report. I tried creating a field in an expense custom form and adding that column to my report but unfortunately it's not returning any data. I also tried creating via textmode in a customized column and had the same result - no value. Any suggestions? Thanks, Linden

[BarryBu2]

If the column is blank, it's probably a minor error in the formula - sometimes case sensitivy or some either minor thing. Perhaps post the code of your custom column and someone might pick it up.

[BarryBu2]

By the way, we've added to custom fields to ours (Accruals and ETC) and have a custom field called 'Variance at Completion' that is: SUB(Planned Amount,SUM(Accruals,ETC,Actual Amount)) So in theory, SUB(Planned Amount,Actual Amount) should be what you want.

[LindenRh]

Thank you, Barry! I have exactly what you suggested below in the custom form field: SUB(Planned Amount,Actual Amount) The field is within an expense type custom form. I just added the column to my expense view within the task and it's actually calculating (which is great). The problem is when I am applying it to the expense report I am not seeing any values... I'm stumped. -Lin

[BarryBu2]

That's weird. A few questions to start troubleshooting: Are you adding it from the interface or in text mode? What format is the custom field (text/currenty/number - presuming we can rule out date and time/date)? Could you share the report defintion and/or a screenshot of it?

[EricLu2]

{{Linden}} Would you kindly send a screen shot of the formula you are using in the custom Expense form? Thanks, Eric

[LindenRh]

Addressing both Barry's and Eric's questions below: The field is build from the expense custom form interface - not textmode within a custom column I've attached screenshots to show: 1. The calculated field (number format) within the expense custom form 2. The field/ column working and returning a value within the expense tab of the task (where the expense is housed) 3. The textmode of the column in my expense report. No edits here, this is just the DE field added to the expense report and then I switched over to textmode Thanks all, Lin

[BarryBu2]

I suspect the blanks are when you look at expenses that don't have the custom form attached. (There is also a check box under the formula bit in setup to say 'recalculate existing values' It doesn't hurt to ensure that is ticked when you save the form). To test this, create a view that includes an editable field from the custom form (like the vendor drop down) and the problematic Funds Variance form. Select a vendor on one that has a blank funds variance. If (after refreshing your screen if necessary) it suddenly works, you need to attach the form to your existing expenses.

[BarryBu2]

I don't know if you ever resolved this, but having just created a variance column on reports directly (no custom forms / calculating fields) the syntax that I'm using is: aggregator.displayformat=currencyStringCurrencyRounded aggregator.function=SUM aggregator.namekey=actualAmount aggregator.valueexpression=SUB({plannedAmount},{actualAmount}) aggregator.valueformat=doubleAsDouble displayname=Variance linkedname=direct namekey=actualAmount querysort=actualAmount valueexpression=SUB({plannedAmount},{actualAmount}) valueformat=currencyStringCurrencyRounded styledef.comparison.leftmethod=double(actualAmount) styledef.comparison.operator=gt styledef.comparison.operatortype=double styledef.comparison.rightmethod=double(plannedAmount) styledef.comparison.truetext=font-weight:900;color:#990000; textmode=true

[MollieSh]

This is great for the Details tab, but is there anyway we can get it to show up in Summary sheet as well? Right now it comes up as seen in the screen shot below. Thanks! Mollie Shatek JLL