Need Help: Value Expression to calculate range of Calculated Field

[JaclynRe]

Hi Reporting Gurus, I am trying to create a value expression, but I have not been successful in getting it to work. I thought I would seek the collective genius of this community to hopefully help me out. Here is my goal: I have a calculated custom field that calculates the age a project has been in a custom status of Returned to Sales. This formula is simple: ROUND(DATEDIFF($$NOW,Date Return to Sales)) So, I get the total days the project has been Returned to Sales. Now, I am trying to create a formula in a report that will tell me if the project has been Returned to Sales for: 0-1 month 2-3 months 4-6 months 7-12 months 1-2 years 2 years + So... The value expression I have been playing with is: description=RTS Time Lapse displayname=RTS Time Lapse textmode=true valueexpression=IF({DE:RTS Age}>=0&&{DE:RTS Age}<=30,'0-1 month',IF({DE:RTS Age}>30&&{DE:RTS Age}<=91,'2-3 months',IF({DE:RTS Age}>91&&{DE:RTS Age}<=182,'4-6 months',IF({DE:RTS Age}>182&&{DE:RTS Age}<=365,'7-12 months',IF({DE:RTS Age}>365&&{DE:RTS Age}<=730,'1-2 years',IF({DE:Age}>730,'2 years +',")))))) valueformat=HTML And for full transparency, my inspiration for this formula was (which works): valueexpression=IF({percentComplete}>=0&&{percentComplete}<=25,'0-25%',IF({percentComplete}>25&&{percentComplete}<=50,'26-50%',IF({percentComplete}>50&&{percentComplete}<=75,'51-75%',IF({percentComplete}>75&&{percentComplete}<=100,'76-100%','')))) Can this be done with a calculated field? Or is my formula just all messed up? Full disclosure, I really start to find myself "out of my wheelhouse" when I start getting into these formulas, so, any recommendations or advice is really appreciated! Jaclyn Reiter, PMP, SA Project Manager, Strategic Initiatives Equifax, Inc. St. Louis, MO 314-684-2693

[KathyLe1]

Hi Jaclyn! Congratulations on working through this beast! You are SO close. I see a couple of things that I think will fix your problems. First, the field name at the very end (prior to the >730) says "DE:Age" rather than "DE:RTS Age". Lastly, the the character just prior to all your closing parentheses is one double quote, as opposed to two single quotes. I think if you change these two things, it should work for you. Kathy Kathy Leeman Comcast Corporation - NVDE

[VincentGo]

Jaclyn, FYI, you may going to run into some problems with using a calculated field for date-differences. In my experience, the calculated field only refreshes when the task/issue is updated. So you may run into problems where you have a task/issue that's clearly 3 months old, but still shows "0-1 months" because you haven't touched the task/issue in a while. I talked with Workfront about this, and the fields don't continuously update for performance reasons. I have a similar formula as you. I have a custom field to calculate a DATEDIFF called [Current Age Months]. It calculates the date difference between TODAY and the [Entry Date], and then groups them into a literal named "Less than 1 month", "1-2 months", "2-4 months", "4+ months". For example, in my attached image, the first line item was submitted on 7/28/17, making it 3 months old. However, the request hasn't been updated since 9/20/17, so it says it's only 1-2 months old. Compare that to line 3, which also was submitted on 7/28, but was last updated on 11/8. There the custom field calculation was refreshed, and it's showing 2-4 months. I know I can select these items and do "Recalculate Custom Expressions", but these custom fields should refresh automatically, not with a manual refresh. Also, items that are in an approval workflow are locked, so the Custom Field calculation gets locked and can't be updated. Vincent Goodwin The Capital Group Companies

[Doug_Den_Hoed__AtAppStore]

Good points Vincent! Jaclyn, for date related calculations where "staleness" is a concern, you might consider: Using "https://support.workfront.com/hc/en-us/articles/115000497647-Calculated-Custom-Data-vs-Calculated-Columns">Calculated Columns vs Calculated Custom Data Colorizing data to indicate its age (e.g. from Hot Pink to Icy Blue, as I shared earlier this week in "https://community.workfront.com/discussions/community-home/digestviewer/viewthread?MessageKey=fbaa44f7-e366-4235-b6ec-a20bf011a014&CommunityKey=aaafaff0-5e4e-4e38-8903-f1f990935567&tab=digestviewer#bmfbaa44f7-e366-4235-b6ec-a20bf011a014#bm0">The Scoop ) Leveraging our manual "http://store.atappstore.com/product/recalc-helper/">Recalc Helper or schedulable "http://store.atappstore.com/product/force-custom-data-double-recalc/">Double-Recalc Custom Parameter Values solutions Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[JaclynRe]

Pardon the abbreviation, but OMG! It finally works! Thank you so much for helping me to correct the formula. Thank you for alerting me of the issues with the calculated fields not dynamically refreshing. I will go back to the requester to alert them of the issues around calculated fields, then we can determine if they want to own recalculating custom expressions manually prior to sending the report, or utilizing the AtAppStore Recalc Helper solutions. I truly appreciate the help. Yay!! Jaclyn Reiter, PMP, SA Project Manager, Strategic Initiatives Equifax, Inc. St. Louis, MO 314-684-2693