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
Laxman1975Laxman1975 

Help need to reduce formula compile size

Hi All,

I have create a formula which will give me total of last 12 months. I have 13 columns for storing data which include January to December data and 1 month i.e from Last year.

Based on Current Date formula should give me last 12 months total.

Here is formula - 

CASE(MONTH( TODAY()), 
1, Feb__c + Mar__c + Apr__c + May__c + Jun__c + Jul__c + Aug__c + Sep__c + Oct__c + Nov__c + Dec__c + LM__c,
2, Jan__c + Mar__c + Apr__c + May__c + Jun__c + Jul__c + Aug__c + Sep__c + Oct__c + Nov__c + Dec__c + LM__c,
3, Jan__c + Feb__c + Apr__c + May__c + Jun__c + Jul__c + Aug__c + Sep__c + Oct__c + Nov__c + Dec__c + LM__c,
4, Jan__c + Feb__c + Mar__c + May__c + Jun__c + Jul__c + Aug__c + Sep__c + Oct__c + Nov__c + Dec__c + LM__c,
5, Jan__c + Feb__c + Mar__c + Apr__c + Jun__c + Jul__c + Aug__c + Sep__c + Oct__c + Nov__c + Dec__c + LM__c,
6, Jan__c + Feb__c + Mar__c + Apr__c + May__c + Jul__c + Aug__c + Sep__c + Oct__c + Nov__c + Dec__c + LM__c,
7, Jan__c + Feb__c + Mar__c + Apr__c + May__c + Jun__c + Aug__c + Sep__c + Oct__c + Nov__c + Dec__c + LM__c,
8, Jan__c + Feb__c + Mar__c + Apr__c + May__c + Jun__c + Jul__c + Sep__c + Oct__c + Nov__c + Dec__c + LM__c,
9, Jan__c + Feb__c + Mar__c + Apr__c + May__c + Jun__c + Jul__c + Aug__c + Oct__c + Nov__c + Dec__c  + LM__c,
10, Jan__c + Feb__c + Mar__c + Apr__c  + May__c + Jun__c + Jul__c + Aug__c + Sep__c + Nov__c + Dec__c + LM__c,
11, Jan__c + Feb__c + Mar__c + Apr__c + May__c + Jun__c + Jul__c + Aug__c + Sep__c + Oct__c + Dec__c + LM__c,
12, Jan__c + Feb__c + Mar__c + Apr__c + May__c + Jun__c + Jul__c + Aug__c + Sep__c + Oct__c + Nov__c +  LM__c,
0)

So I have check month number based on current date and perform sum of last 12 months(fields) data. But formula compile size execeed 5000 limit. Can some one help me to reduce this size?

Or any other way to do that using formula or workflow? I dont want to build any trigger just for this calculations.

Thanks in advance.

Regards
Laxman
Dhanya NDhanya N
Hi Laxman,

Solution is creating helper fields.
Create one custom formula field(Tatal__c) of type 'Number' and calculate total value: 
Jan__c + Feb__c + Mar__c + Apr__c + May__c + Jun__c + Jul__c + Aug__c + Sep__c + Oct__c + Nov__c + Dec__c + LM__c
Then in your custom field use this helper field. i.e  
​Tatal__c - CASE(MONTH( TODAY()), 
1, Jan__c,
2, Feb__c,
3, Mar__c,
4, Apr__c,
5, May__c,
6, Jun__c,
7, Jul__c,
8, Aug__c,
9, Sep__c,
10, Oct__c,
11, Nov__c,
12, Dec__c,
0)
Let me know if it works.

Thanks,
Dhanya
Dhanya NDhanya N
Let me know if you face any issue.