I'm recreating a project methodology in Workfront using a series of calculated fields to identify 6 Sigma variances for the tasks on the projects critical path. The final step is to calculate the Standard Deviation as the square root of the sum of the variances.
Everything works until I add SQRT as the aggregator function in the column.
Does anyone know if this is possible?
Thanks -
Teale
Topics help categorize Community content and increase your ability to discover relevant content.
Views
Replies
Total Likes
Curious if someone can figure this out. The part I'm getting stuck at is that you want the square root of the sum of the variances. So you need both a SUM and SQRT to happen in the same aggregation.
Views
Replies
Total Likes
Yes, exactly... so this is what I have
aggregator.displayformat=customNumberAsString
aggregator.function=SUM
aggregator.namekey=PERT Variance
aggregator.valueexpression=ROUND(SUM(SQRT({DE:PERT Variance}),2))
aggregator.valueformat=HTML
displayname=Project Standard Deviation
linkedname=direct
namekey=PERT Variance
querysort=DE:PERT Variance
textmode=true
valueexpression=ROUND(SUM(SQRT({DE:PERT Variance}),2))
valueformat=HTML
I get the SUM in the Grouping header but not the square root.
Not sure why it won't work.
The valueexpression for aggregator is still looking at one field on one line item. It is just saying, instead of looking at one valuefield, look at this calculation instead. The true "aggregation" is done with the aggregator.function line. In this case you said SUM so it is adding all the items in the grouping.
You can try changing aggregator/.function to SQRT, but I don't think that will work as there is still nothing adding all the items in the grouping together. But I might be wrong. ;)
SIDE NOTE: I think your formula is wrong. As it is, it is taking the square root of PERT Variance, adding 2, and then rounding to (i'am assuming) zero. Since you aren't adding to different fields together, the SUM function won't work. Besides I believe you need to SUM everything up first before doing the square root and this is just summing the square roots, so you'd get a different number than you want.
Views
Replies
Total Likes
Yes and No... When I changed the aggregation function to SQRT, it doesn't work but it does correctly give me the sum.
Thanks for your help!
Views
Replies
Total Likes
Hi Teale and Anthony,
Following, and I suspect that unlike Excel (or Google Sheets <wink>) where aggregates can be "chained" in this fashion, within Workfront, only a single aggregation can be performed (so SQRT, or SUM, but not both).
As a workaround (either manually, using our UberCalc solution, or Fusion), you could periodically calculate the SUM of all the Task PERT Variances, store them in custom data (e.g. at the Project level), and then in a separate custom calculated parameter (at that level), take the SQRT.
Regards,
Doug
P.S. I might be misunderstanding, but noticed you said "square root of the sum of the variances" in your original post, but from the formula, seem to be taking the "sum of the square roots"...either way, though, same problem (2 aggregators), similar workaround
Views
Replies
Total Likes