SQRT Function to Aggregate in Calculated Column?

[TealeMcCleaf]

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

[AnthonyIm]

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.

[TealeMcCleaf]

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.

[AnthonyIm]

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.

[TealeMcCleaf]

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!

[Doug_Den_Hoed__AtAppStore]

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