Valueexpression help using task duration field

[NickVa1]

Hi WF Community,

I'm looking to create a text mode field on a task report and calculate the difference (variance) between the Duration field and the Actual Duration field. It looks like it should be a simple text mode valueexpression (SUB(number1,number2,...); however, it doesn't seem to be working for me.

Here's what I have (I used modified a different text mode example using planned and actual completion dates):

descriptionkey=duration

displayname=Actual Duration Variance (Days)

linkedname=direct

listsort=duration

namekey=duration

querysort=duration

shortview=false

stretch=45

styledef.case.0.comparison.attribute=css

styledef.case.0.comparison.isrowcase=false

styledef.case.0.comparison.leftmethod=duration

styledef.case.0.comparison.lefttext=duration

styledef.case.0.comparison.operator=notnull

styledef.case.0.comparison.operatortype=number

styledef.case.0.comparison.righttext=

styledef.case.0.comparison.trueproperty.0.name=bgcolor

styledef.case.0.comparison.trueproperty.0.value=dcf6f7

styledef.case.0.comparison.truetext=

styledef.case.0.comparison.usefield=false

textmode=true

valueexpression=SUB(actualDuration,duration)

valueformat=HTML

If anyone knows where I might be going wrong, help would be appreciated. The field is just blank when I use this.

Thanks.

Nick

[TerryHy]

Try this for your valueexpression - added the curly brackets around each value.

valueexpression=SUB({actualDuration},{duration})

[NRYNR]

Hi Nick, in addition to Terry's function, I suggest you give the WFPro Text Mode examples a look. Specifically, the "Entry to Completion Date Variance" example has some calculations that should get you in the right direction.

[NickVa1 · Accepted solution]

Thanks, Terry and Narayan.

Terry, that got me to where I needed; however, it gave me a result in minutes based on an 8-hr workday. I have to convert that to days, and then use the ROUND expression to get a decimal.

Here's what I ended up with if anyone in the future is looking to use it:

descriptionkey=duration

displayname=Actual Duration Variance (Days)

linkedname=direct

listsort=duration

namekey=duration

querysort=duration

shortview=false

stretch=45

styledef.case.0.comparison.attribute=css

styledef.case.0.comparison.isrowcase=false

styledef.case.0.comparison.leftmethod=duration

styledef.case.0.comparison.lefttext=duration

styledef.case.0.comparison.operator=notnull

styledef.case.0.comparison.operatortype=number

styledef.case.0.comparison.righttext=

styledef.case.0.comparison.trueproperty.0.name=bgcolor

styledef.case.0.comparison.trueproperty.0.value=dcf6f7

styledef.case.0.comparison.truetext=

styledef.case.0.comparison.usefield=false

textmode=true

valueexpression=ROUND((((SUB({actualDuration},{duration}))/60)/8),1)

valueformat=HTML

[MackenzieGr]

@NRYN R - inactive‚ -- Thank you so much for your Text Mode response from above. I am building my first WorkFront report and the request is to have a column that shows the variance percentage from Projected Completion date to Actual Completion date. I used the link you shared to get pretty far, but I don't know how to adjust the Text Mode to convert from days to percentages and I am not sure who to reach out to. This is what I have so far;

aggregator.function=AVG

aggregator.namekey=datevariance

aggregator.valueexpression=WEEKDAYDIFF({projectedCompletionDate},{actualCompletionDate})

aggregator.valueformat=HTML

description=Projected Completion to Actual Completion Date

displayname=Date Variance (Projected to Actual Completion Date)

namekey=datevariance

textmode=true

valueexpression=ABS(WEEKDAYDIFF ({projectedCompletionDate},{actualCompletionDate}))

valuefield=dateVariance

valueformat=HTML