Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Coral RacingCoral Racing 

How to calculate rolling financial week year on year

Hello.

I have used the following formula to work out what Financial Week a ticket was opened with our Support Desk using the following formula:
MOD(FLOOR( ( DATEVALUE( CreatedDate ) - DATE(2014,09,21)-6)/7),52)+1
Our 2014/15 Financial calendar started 2014-09-21 (Fri) which works great.   However what I need it to do is reset  back to 1 when we start the next Financial year 2015-09-25 and reset again 2016-09-23 and so on............

Any ideas?
James LoghryJames Loghry
I've been going back and forth on this one quite a bit.

Generally speaking the declaritive approach would be to dynamically calculate the latter half of your formula based on the CreatedDate.  You would use an IF or CASE function for determining if the CreatedDate was less than 9/21 of the given year, and using something like DATE(YEAR(CreatedDate)+1) if that was the case.

However, this approach lends itself to a LOT of complexity.  Not only do you need to worry about calculating which part of which year the "fiscal week" lies on (e.g. last third of 2015 or first two thirds of 2016), but you would also need to add business days calculations to determine which exact date the fiscal year starts on (is that Monday on 9/21 or 9/25?)

You probably could do this via a formula (or using the standard fiscal year functionality in Salesforce), however, it would be quite complex and messy.

Instead, I would suggest creating a custom setting record with start and end dates for your fiscal years.  Then you would use either a Process/Flow or an Apex Trigger to populate a custom field (say FISCAL_YEAR_START__c) for the fiscal year start date on the Case or Ticket object.   You would then use the following formula to calculate the "Fiscal Week" of the ticket:
  
MOD(FLOOR( ( DATEVALUE( CreatedDate ) - FISCAL_YEAR_START__c-6)/7),52)+1

Note: if you go with this approach, you will need to update existing ticket records with the correct values for FISCAL_YEAR_START__c dates.
Coral RacingCoral Racing
Hi James
Thanks for taking the time to reply.

How would I go about creating a custom setting record with start and end dates for your fiscal years?  Basic stuff I am sure but learning as I go.....

Many Thanks

Sonya
James LoghryJames Loghry
You'll need to create a "List" custom setting (not a hierarchy custom setting) for this case.  You can find the setup for custom settings by going to Setup->Develop->Custom Setttings, and creating the new List custom setting.  From there you can add fields to it, such as the start and end dates. 

After you create the custom setting, you'll click on the "Manage" button to populate the custom setting with your fiscal years.  Use your fiscal year (such as 2015) for the name of the record.

Then in an Apex trigger, you could reference the custom setting as follows:
  
trigger FiscalYearUpdate on Case(before insert){
    List<My_Fiscal_Year__c> fiscalYears = My_Fiscal_Year__c.getAll().values();
    for(Case c : Trigger.new){
        for(My_Fiscal_Year__c fy : fiscalYears){
            if(c.CreatedDate >= fy.Start_Date__c && c.CreatedDate <= fy.End_Date__c){
                c.FISCAL_YEAR_START_DATE__c = fy.Start_Date__c;
            }
        }
    }
}

Note, I'm making lots of assumptions in how you would name the custom setting and custom fields in this example.

Also, custom settings are just like any other data in that you will need to populate custom setting records for any unit tests to work properly.  If you're familiar with creating data for unit tests, creating the custom setting records is a similar process to creating any sobject in a unit test.