Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Valueexpression help using task duration field

Avatar

Level 9

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

Topics

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

1 Accepted Solution

Avatar

Correct answer by
Level 9

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

View solution in original post

4 Replies

Avatar

Level 7

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

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

Avatar

Level 10

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.

Avatar

Correct answer by
Level 9

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

Avatar

Level 1

@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;0694X00000CIfyyQAD.png

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