Expand my Community achievements bar.

Subtract Two Columns in a View

Avatar

Level 2

I am trying to subtract two columns in a view. Column 1 is the traditional "Planned Expense Cost" Field and looks like this in text mode:

aggregator.displayformat=currencyStringCurrencyRounded

aggregator.function=SUM

aggregator.namekey=plannedExpenseCost

aggregator.valuefield=plannedExpenseCost

aggregator.valueformat=doubleAsDouble

displayname=

linkedname=direct

namekey=plannedExpenseCost

querysort=plannedExpenseCost

valuefield=plannedExpenseCost

valueformat=currencyStringCurrencyRounded

Column 2 is shown below in text mode:

displayname=

linkedname=direct

namekey=Planned Capital Cost

querysort=DE:Planned Capital Cost

valuefield=Planned Capital Cost

valueformat=customNumberAsCurrencyStringRounded

I want to subtract column 2 from column 1 and display the value. I thought the formula should be simple, but I am not receiving a result. Here is what I have:

displayname=Expense Cost

textmode=true

valueexpression=SUB({plannedExpenseCost},{plannedCapitalCost})

valueformat=customNumberAsCurrencyStringRounded

Can anyone help with what I am missing?

Thank you!

Cori

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

2 Replies

Avatar

Community Advisor

Hi Cori,

For such formulas, within the curly braces, Built-In Workfront (aka Native, aka Core) fields use camelCase (e.g. Planned Expense Cost becomes {plannedExpenseCost}), whereas User-Defined Custom (aka User Defined aka Data Extension aka DE) parameters use the DE: prefix (e.g. Planned Capital Cost becomes {DE:Planned Capital Cost}). Accordingly, I suggest you try this instead:

valueexpression=SUB({plannedExpenseCost},{DE:Planned Capital Cost})

Also , if you're at liberty to comment, I'd be interested in learning more about how you're planning to use Workfront for Capital projects, as I've spent a material portion (bah-dump-dump) of my career on such concepts, such as our Capital Tracking solution, for you and others reading who might be interested.

Regards,

Doug

Thank you Doug! This worked!

I am not currently doing anything fancy with Capital. We are doing our budgeting in Workfront and we keep Capital and Expense budget separate. I've had to create categories to do this and can easily report on each individually but wanted my "Project Expense" in my Projects View to only show "Expense" and not "Capital" because it is confusing people.

Thanks for your help!

Cori