Skip to main content
Topics#Reports & DashboardsDomanda di Jasmine Aulakh
Jasmine Aulakh (Purus) ha fatto una domanda in #Reports & Dashboards
29 apr 2019, 11:08

Reference dynamic close date in formula for custom quota calculations

I am trying to create a formula which references a "dynamic" close date on Opportunities. The aim of the game is that the correct Quota for a sales rep will be pulled through, depending on Opportunity Close Date. I want to keep this quota field to report against on a dynamic dashboard.

 

The dynamic part of the formula is based on year (see below) - if the close date is less than a certain date parameter, the field on Opportunity should reference the correct Quota value (E.g. if Q1 Quota, this is calculated as Annual Quota * Q1 Quota % - this data is held on the Quota object and referenced in the formula below)

 

The year needs to be dynamic as I would like this to be future proofed 

 

IF(CloseDate < DATE(YEAR(TODAY()),10,31), Quota__r.Q1_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),11,30), Quota__r.Q1_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),12,31), Quota__r.Q1_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),1,31), Quota__r.Q2_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),2,29), Quota__r.Q2_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),3,31), Quota__r.Q2_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),4,30), Quota__r.Q3_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),5,31), Quota__r.Q3_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),6,30), Quota__r.Q3_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),7,31), Quota__r.Q4_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),8,31), Quota__r.Q4_Quota__c,

 

IF(CloseDate <= DATE(YEAR(TODAY()),9,30), Quota__r.Q4_Quota__c

 

,0

 

))))))))))))

 

What am I doing wrong?
1 risposta
  1. Sunil Sarilla (M&G Investments)
    29 apr 2019, 11:29
    Just replace the YEAR(TODAY()) with YEAR(CloseDate) in the formula and see if that helps.

     

    also, is your requirement if the closedate falls in a certain quarter use that quarter quota for the calculations?
Scrivi una risposta...
0/9000