Creating Custom Time Frame Formulas for Reporting and Filtering
Learn how to create custom formula fields to calculate time periods such as calendar years, fiscal years, quarters, months, weeks, and days for reporting and filtering purposes. Understand offset calculations to determine the date's proximity to the current time period and implement to-date calculations to check if a date falls within a specific timeframe.
Uploaded on Dec 13, 2024 | 0 Views
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
1 Filter + 1 Formula = Any Time Frame You Want
Create a New Formula Field Go to the object that has the date field you want to use and create a new formula field This will reference your date field many times in order to perform various calculations and return a text string This text string result is used in the report or dashboard filter Name it something like [your date field] Relative Time Periods
Choose Time Periods Calculate a value for every time period you want to use in filters Calendar years Fiscal years Quarters Months Weeks Days Consider: Business Days (don t count weekends) Consider: Fiscal Months (that don t start on the 1stof each calendar month) Consider: Fiscal Quarters (that don t start on Jan 1, Apr 1, Jul 1, Oct 1) Surround each calculation with a delimiter I chose pipes | | FY | | M | W | D | Q CY
Offset Calculations Determine how offset your date value is from today If the date is within the current time period, then there is 0 offset If the date occurred in the previous time period, then there is -1 offset If the date occurred two time periods ago, then there is -2 offset Etc Use positive numbers for dates in the future | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
To-Date Calculations These act like checkboxes within the text string If the date is to-date , append TD Otherwise the TD is not appended Determine if the date is to-date even if it s in a different time period For calendar years: if the date is between Jan 1 and today during any year, it s year- to-date For calendar months: if the date is between the 1stand today s day of the month during any month, it s month-to-date For calendar quarters: if the date is between the beginning of the quarter and today s point in the quarter during anyquarter, it s quarter-to-date Consider: You could extend this to day-to-date if you are using a date/time field and want to calculate if the date/time has occurred between midnight and now() of any day | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Calendar Year Offset Calculation TEXT( YEAR(Date__c) - YEAR(TODAY()) ) 2017 2018 2019 | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Calendar Year-to-Date Calculation Jan Feb Mar IF( MONTH(Date__c) < MONTH(TODAY()) || ( MONTH(Date__c) = MONTH(TODAY()) && DAY(Date__c) <= DAY(TODAY()) ), "TD", "" )| ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D | 2017 2018 2019
Fiscal Year Offset Calculation TEXT(YEAR(Date__c) + IF( MONTH(Date__c) > Fiscal_Year_Start_Month || ( MONTH(Date__c) = Fiscal_Year_Start_Month && DAY(Date__c) >= Fiscal_Year_Start_Day ), 1, 0 ) FY17 FY18 FY19 | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Fiscal Year Offset Calculation (continued) - (YEAR(TODAY()) + IF( MONTH(TODAY()) > Enter_Your_Fiscal_Year_Start_Month_Here || ( MONTH(TODAY()) = Enter_Your_Fiscal_Year_Start_Month_Here && DAY(TODAY()) >= Enter_Your_Fiscal_Year_Start_Day_Here ), 1, 0 )) ) FY17 FY18 FY19 | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Fiscal Year-to-Date Calculation IF( MOD(MONTH(Date__c) + 12 - Fiscal_Year_Start_Month, 12) < MOD(MONTH (TODAY()) + 12 - Fiscal_Year_Start_Month, 12) || Apr May Jun ( MONTH(Date__c) = MONTH(TODAY()) && DAY(Date__c) <= DAY(TODAY()) && FY17 0 1 2 ( Fiscal_Year_Start_Day <= DAY(Date__c) || Fiscal_Year_Start_Day > DAY(TODAY()) FY18 0 1 2 ) ), "TD", "" FY19 0 1 2 ) | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Quarter Offset Calculation TEXT( CEILING(MONTH(Date__c) / 3) - CEILING(MONTH(TODAY()) / 3) + 4 * (YEAR(Date__c) - YEAR(TODAY())) ) Q1 Q2 Q3 | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Quarter-to-Date Calculation IF( MOD(MONTH(Date__c)-1, 3) < MOD(MONTH(TODAY())-1, 3) || ( MOD(MONTH(Date__c)-1, 3) = MOD(MONTH(TODAY())-1, 3) && DAY(Date__c) <= DAY(TODAY()) ), "TD", "" ) Q1 Jan 0 Feb 1 Mar 2 Q2 Apr 0 May 1 Jun 2 Q3 Jul 0 Aug 1 Sep 2 | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Month Offset Calculation TEXT( MONTH(Date__c) - MONTH(TODAY()) + 12 * (YEAR(Date__c) - YEAR(TODAY())) ) Jan Feb Mar | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Month-to-Date Calculation IF( DAY(Date__c) <= DAY(TODAY()), "TD", "" ) Jan Feb Mar Apr May Jun Jul Aug Sep | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Week Offset Calculation TEXT( ( (Date__c - WEEKDAY(Date__c)) - (TODAY() - WEEKDAY(TODAY())) ) / 7 ) | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D | Week 1 Week 2 Week 3 Week 4 Week 5
Week-to-Date Calculation IF(WEEKDAY(Date__c) <= WEEKDAY(TODAY()), "TD", "" ) Jan Feb Mar Apr May Jun Jul Aug Sep | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Day Offset Calculation TEXT( Date__c - TODAY() ) | ##CYTD | ##FYTD | ##QTD | ##MTD | ##WTD | ##D |
Putting it all together The combined formula can get pretty long Be sure to include your time frame indicator in the correct spot Don t forget the delimiters! You can use comments to keep track of each calculation /* insert your comment here*/ Concatenate each calculation with ampersands (&) The notes section of this slide has a complete example
Example Today is July 31, 2019. The date value is May 1, 2019. (FY starts Apr 1) 0 calendar years ago, year-to-date 0 fiscal years ago, fiscal-year-to-date 1 quarter ago, not quarter-to-date 2 months ago, month-to-date 13 weeks ago, week-to-date 91 days ago | 0CYTD | 0FYTD | -1Q | 2MTD | 13WTD | 91D |
