function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ahasslerahassler 

Report Custom Formula

I'm not sure how to write a custom formula in my opporutnities report... Here's what I want:

If the close date is between 1-2 years ago use the sum of "1Y RP" field and divided by 2 (if not true then use 0), and if the close date is between 7-12 months ago use the sum of "7 RP" field (if not true then use 0), and if the close date is within the past 6 months, use the "6 RP" field (if not true then use 0), but I want the the final output to be all three of those numbers added together. 

In case it is helpful, the Field Label: 6 RP has an API Name of: x6_RP_c. Similar set up for 1Y RP and 7 RP.

 

Jason Curtis NBSFDGJason Curtis NBSFDG
Hi, your best best is going to be to create a formula field on the Opportunities object that does the math for you and then just include that single field on the report. That way you can total/sum that field in the same column. WIll this work for you?
FYI, the Success Community (success.salesforce.com) is a great resource for reporting questions.
ahasslerahassler
So within each opporutnity there is a single 1Y RP field, a single 7 RP field, and a single 6RP field. I could just sum/total the individual fields already in a report. What I need is a formula that looks across all opportunities and adds some of the 1Y RP fields, some of the 7RP fields and some of the 6RP fields together based on parameters I set. I could write a formula that does it and make it a field, but why? Then it would show up on every opporutnity and I really only want it on one report. Mostly, I'm having trouble writing the formula less with the logistics of where to use it, so if you have any recommendations on the formula that would be really helpful!
Jason Curtis NBSFDGJason Curtis NBSFDG
You won't be able to write a formula like you are thinking inside of a report. Best to create it as a field, and remove the field on any layouts.
Basically though you create a formula field on Opportunity with a few "if" statements, I created some fields and worked this up on a dev org:
IF(AND(( TODAY() - CloseDate > 364),(TODAY() - CloseDate < 731)), X1Y_RP__c / 2, 
IF(AND(( TODAY() - CloseDate < 365),(TODAY() - CloseDate > 182)),  X7_RP__c ,  
IF(( TODAY() - CloseDate < 182),  X6_RP__c , 0) ))
Once you have this field you can use it on reports as a single column and summarize off of it.