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
Sylvie SerpletSylvie Serplet 

Formula field to retrieve data for previous month

Hi all,
I have a custom object where I enter every month sales data. I have the need to display on each record the value of the same field for the previous month. The record need also to fullfill other criteria such as same sales name, same year....
I have created a formula field but it is not working. Any thought? 
IF( 
AND( 
Year__c = YEAR(DATEVALUE(CreatedDate)), 
MONTH(DATEVALUE(CreatedDate)) = MONTH(TODAY()) -1, 
Sales_Team__c = Sales_Team__c, 
Branch__c = Branch__c, 
), 
No_of_Clients__c, null 
)
Thank you in advance for your help.
Sylvie
Alain CabonAlain Cabon
Hello,
  • The previous month is a little more complicated because the previous month of january is december of the previous year.
  • Sales_Team__c = Sales_Team__c, Branch__c = Branch__c seems useless or it is a trick that I don't know but it is not your question.
  • A formula doesn't perform a research of a value in the data according the conditions (here the No_of_Clients__c for the previous month). 
  • A formula just evaluates a new value with the existing fields of the same record (and its parents).
  • There is an exception with VLOOKUP() for a validation rule and that is useless here.
If you want the value of the No_of_Clients__c for the previous month next to the No_of_Clients__c of a month (without using a list of months), you need to code a SOQL query with the converted condition of : AND (Year__c = YEAR(DATEVALUE(CreatedDate)), MONTH(DATEVALUE(CreatedDate)) = MONTH(TODAY()) -1, Sales_Team__c = Sales_Team__c, Branch__c = Branch__c) for the filter (WHERE)
and a small Visualforce page to use the found value "on the fly" into your detail record page.

It is not so difficult to do with an extension of a standard controller (Apex) with a little VFP.

SELECT No_of_Clients__c
FROM MyObject__c
WHERE CreatedDate >= 2018-06-00T00:00:00Z 
AND CreatedDate < 2018-07-00T00:00:00Z
AND Sales_Team__c = 'SSSSS' AND Branch__c = 'BBBBB'

If I am not totally wrong and there is no other simpler alternatives given here, we can build the complete solution in the next comments if you are not comfortable with Apex and a VFP.

The complicated part is to build the values: 2018-06-00T00:00:00Z and 2018-07-00T00:00:00Z in the apex controller.
Sylvie SerpletSylvie Serplet
Hi Alain,
Thank you for the response. Do you think I can achieve the same result with a trigger populating a text field?
Sylvie
Alain CabonAlain Cabon
Hello,

A trigger could be enough indeed (on after create, on delete, on undelete).

You can calculate the previous/next values during the creation of a new record and its deletion (and undelete).

A text field populating with a trigger has the advantage that you can use it in reports.

When you create a record, you will find easily the previous No_of_Clients__c.

The update doesn't change the created date.

When you delete or undelete a record, you need to look for a potential record in the next month for wich the previous No_of_Clients__c could be needed (cleared or updated)..

A trigger is more complicated to write and must be well writen (bulkified) but that will also work and there is many common examples for this kind of task. 
 
Sylvie SerpletSylvie Serplet
Thank you Alain. I will give it a try.
Alain CabonAlain Cabon
Hello,

after create is after insert precisely.

You can try to write a first trigger but it is a difficult exercise (dates, delete/undelete, insert) and post a new question to fix it if that doesn't work as expected (or post your code here and I wll fix it).

The problem of a trigger is that it can also be called by batch processings and you must not exceed the governor limits (bulkified apex code).

With the Lightning process builder, you can avoid an apex trigger but the delete and undelete events are not detected by this tool. 
The workaround is to block the deletions replacing it with an update using a flag "isActive" or/and dates of validity for excluding them but you will have other problems with an active flag and dates of validity. You keep all the records and purge all the inactive data twenty months after their created dates (isActive=false).  There is not a perfect workaround for the Apex trigger and it is still the simpler solution but difficult to write at the beginning.
https://success.salesforce.com/ideaView?id=08730000000DlPBAA0