workfront-api - Custom View

Question

I'm setting up a dashboard in Workfront. I want to create a custom view that I'm calling "Est Variance" which, at the task level, will compare a tasks planned hours to complete (workRequired) with actual hours to complete (actualWorkRequired). In other words, we planned for 10 hours but it took 15, so the value displayed should be +50%.

The calculation is Planned Hours (minus) Actual Hours (divided by) Planned Hours. I came up with the following code for the view:

displayname=Est Variance
linkedname=direct
namekey=Est Variance
querysort=actualWork
shortview=true
textmode=true
valueexpression=ROUND(SUB({actualWorkRequired},{workRequired}))/({workRequired})*100
valuefield=actualWorkRequired
valueformat=compound
viewalias=actualworkrequired

... which returns the correct value, but I'm trying to make the following changes:

1. CONCAT a "%" after the value
2. Round to the nearest whole number
3. Add rules that would display any positive value in red, and any negative value in green.
4. For tasks returning "0" (planned hours = actual hours), display nothing.

Answer 1

1.) CONCAT a "%" after the value

2.) Round to the nearest whole number

Setting the valueformat=doubleAsPercentRounded will accomplish both, so simplify the valueexpression to be

valueexpression=SUB({actualWorkRequired},{workRequired})/{workRequired}

3.) Add rules that would display any positive value in red, and any negative value in green.

You can use the conditional formatting to color the results depending on their value. i.e.

styledef.case.0.comparison.icon=false              // show the value instead of the icon
styledef.case.0.comparison.leftmethod=Est Variance // column name
styledef.case.0.comparison.lefttext=Est Variance   // column name
styledef.case.0.comparison.operator=lt             // less than operator
styledef.case.0.comparison.operatortype=double     // data type
styledef.case.0.comparison.righttext=0             // target value
styledef.case.0.comparison.trueproperty.0.name=textcolor  // tranform on true
styledef.case.0.comparison.trueproperty.0.value=03a219  // green
styledef.case.0.comparison.truetext=              // ignore

4.) For tasks returning "0" (planned hours = actual hours), display nothing.

Finally a simple IF statement in the valueexpression can make the value be an empty string when the result is 0

IF(condition, trueStatement, falseStatement)

valueexpression=IF({actualWorkRequired} = {workRequired}, "", SUB({actualWorkRequired},{workRequired})/{workRequired}

Good luck!