SQRT Function to Aggregate in Calculated Column? | Community
Skip to main content
T
TealeMcCleaf
Level 6
June 8, 2021
Question

SQRT Function to Aggregate in Calculated Column?

  • June 8, 2021
  • 2 replies
  • 995 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

2 replies

imgrund
Adobe Employee
imgrundAdobe Employee
Adobe Employee
June 9, 2021

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.

T
TealeMcCleafAuthor
Level 6
June 9, 2021

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.

    imgrund
    Adobe Employee
    imgrundAdobe Employee
    Adobe Employee
    June 9, 2021

    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.

    Doug_Den_Hoed_AtAppStore
    Community Advisor
    Doug_Den_Hoed_AtAppStoreCommunity Advisor
    Community Advisor
    June 9, 2021

    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

    Terms & ConditionsAccessibility statement

    Loading footer...