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?
Topics help categorize Community content and increase your ability to discover relevant content.
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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
Views
Replies
Total Likes
Man, "Anthony" and "Doug" sure are going to a lot of trouble to make it appear that we are two separate people...
Regards,
"Doug"
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.
Views
Replies
Total Likes
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 :-)
Views
Replies
Total Likes
Views
Likes
Replies
Views
Likes
Replies