Looking for some text mode magic | Community
Skip to main content
Heather_Kulbacki
Community Advisor
Heather_KulbackiCommunity Advisor
Community Advisor
March 9, 2021
Question

Looking for some text mode magic

  • March 9, 2021
  • 5 replies
  • 706 views

I have this text mode column in a report to give me an allocated cost

displayname=Allocated Cost

textmode=true

valueexpression=IF({project}.{DE:GL Code}="Strategic Marketing - 400.5304.8510.83326",{actualCost},IF(CONTAINS("50% 400.5304.8510.83326",{project}.{DE:GL Code}),({actualCost}*.5),IF(CONTAINS("52% 400.5304.8510.83326",{project}.{DE:GL Code}),({actualCost}*.52),IF(CONTAINS("34% 400.5304.8510.83326",{project}.{DE:GL Code}),({actualCost}*.34),IF(CONTAINS("54% 400.5304.8510.83326",{project}.{DE:GL Code}),({actualCost}*.54),IF(CONTAINS("50% Strategic Marketing - 400.5304.8510.83326",{project}.{DE:GL Code}),({actualCost}*.5)))))))

valueformat=currencyStringCurrency

yeah, that's a lot of IFs, but it works.

Is there any text mode magic I can do to aggregate this column with a SUM?

I have an Actual Cost column with a SUM and when I look at the text mode for that, there's a row:

aggregator.valuefield=actualCost

I feel like I should be able to replace that with:

aggregator.valueexpression=my big long IF statement

But I tried that awhile ago and it didn't work. Any thoughts on if this is even possible?

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

5 replies

imgrund
Adobe Employee
imgrundAdobe Employee
Adobe Employee
March 9, 2021

Hi - unfortunately (to the best of my knowledge) you cannot aggregate custom columns. Since the info is not living on a database anywhere, it is hard to get the SUM.

Do all the tasks in your projects have a custom form already? If so, you might want to take your IF statements and put them into a calculated field. Then you can use that to aggregate and get SUM.

Doug_Den_Hoed__AtAppStore
Community Advisor
Doug_Den_Hoed__AtAppStoreCommunity Advisor
Community Advisor
March 9, 2021

Ehm..."impressive" (question mark?) Heather,

Glad it works, but no, I don't believe the SUM function is capable of interpreting the valueexpression in that way (although I'd be happy to be proven wrong). Instead, noting that it would allow for other cool uses (PRO: filter, group, chart, etc.) but does have some some drawbacks (CON: stale data, requires recalculation), I'd suggest moving your IF statement into a calculated currency custom parameter, at which point SUM would then be trivial, off the valuefield.

Regards,

Doug

Doug_Den_Hoed__AtAppStore
Community Advisor
Doug_Den_Hoed__AtAppStoreCommunity Advisor
Community Advisor
March 9, 2021

Man, "Anthony" and "Doug" sure are going to a lot of trouble to make it appear that we are two separate people...

Regards,

"Doug"

    Heather_Kulbacki
    Community Advisor
    Heather_KulbackiCommunity AdvisorAuthor
    Community Advisor
    March 10, 2021

    I was hoping the two of you would respond, or at least I think it's two separate people ;)

    But was guessing I'd get the 'unfortunately, no' answer.

    Currently we're pulling the report to Excel to get the sum, but I'd love to keep people right in Workfront.

    I'll have to put some thought into a task and issue form. Time also gets billed direct to the project but adding another field to one of those forms is easy. And I could set up this group's templates to add that form on any new tasks and issues. But then I'd want a way to prevent project managers from creating a project without a template - pretty sure I've upvoted that idea in the Innovation Lab already.

    I'm going to add this to my notes for my next 1:1 with the New Reporting team for now.

    ChrisBudgen
    ChrisBudgen
    March 15, 2021

    If you are using Fusion or built some custom code to work with the API, you can force the recalc off those fields in automated intervals. This should at least remedy the "stale data" issue :-)

    Terms & ConditionsAccessibility statement

    Loading footer...