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
Matt CzugalaMatt Czugala 

Formula in Opportunity Field to calc YTD sales

In Opportunity, we track monthly sales.  So there is a Jan_Sales, Feb_Sales, etc. for each month.

I want to create a YTD_Sales field so that each month it adds the next month into is.

I figured this was doable with a case statement, but I keep running into errors with formula being too long.

Any suggestions? Thank you

bathybathy
Try this Matt,
Case(month(today),1,jansales__c,2,Jansales__c+febsales__c,3,Jansales__c+febsales__c+marsales__c,....,11,jansales__c+febsales__c+marchsales__c+...Novsales__c,jansales__c+febsales__+...decsales__c)
Please let me know if this generates more errors or mark this as best answer as it iwll help others.

Make sure you declare return value for  this fomula as currency.

Thanks,
srlawr uksrlawr uk
Remember that with formulas, when you save them, they compile into each other, so if Jan_Sales is a formula, and you use it in another formula (as bathy has suggested) then the "contents" (and thus the size) of Jan_Sales is compiled into the wider formula, therefore, in the example given by bathy, if the Jan_Sales formula is 50 bytes, it's being used 12 times, and thus will add 600 bytes to the compiled size of the top level formula, add in Feb_Sales 11 times, and March etc.etc. and you are going to have one heck of a formula at final compile time.


There are some tricks and twists to avoid huge formulae like this, probably -in this case - involving Workflow to copy values from long formula fields into concrete value fields, and then using those in wider formulae going forward.

This of course might not be the case though, and Jan_Sales might just be a number field for you! :)
Matt CzugalaMatt Czugala
@srlawr UK, that is the issue I am running into.  So I am now trying to figure out how to create a workflow rule so that the rule matches on something like year(closedate) = year(today()) so that it only updates opportunities that are running during the current year.  The example forumula bathy posted would run everytime a opportunity is viewed and if the oppotunity closed last year, then YTD should not change.