You need to sign in to do that
Don't have an account?
Rachel Linder 20
Formula Field based on subscription start and end dates not working as expected
We have a formula field on the Subscription record called "ARR Contribution". The formula is:
I created a second ARR Contribution field in sandbox called "ARR Contribution 2" to test a revised formula. The revised formula is:
But if I update the dates so that, for example, the start date is 7/1/2018 and the end date is 6/30/2019, I was expecting that the "ARR Contribution 2" field would be completed because the Prorate Multiplier is blank. But what is happening is that it is putting the $5,000 value in the first "ARR Contribution" field. I would expect that at that point both ARR Contribution fields would say $5,000.
See screenshots below:
Start Date 7/1/2018 and End Date 7/1/2018 (equal dates)
Start Date 7/1/2018 and End Date 6/30/2019 (not equal dates)
Why are my results moving between the two fields? What needs to be tweaked in the formula for my "ARR Contribution 2" field. So, that the expected result for either case populates on the second field.
Total_Amount__c / BLANKVALUE( SBQQ__ProrateMultiplier__c, CEILING((((SBQQ__SubscriptionEndDate__c - SBQQ__SubscriptionStartDate__c) / 365) * 12)) / SBQQ__Product__r.SBQQ__SubscriptionTerm__c )The formula does not calculate properly if the Subscription Start Date and Subscription End Date are equal (ie, 7/1/2019 start date and 7/1/2019 end date). If the dates are not equal it calculates as expected.
I created a second ARR Contribution field in sandbox called "ARR Contribution 2" to test a revised formula. The revised formula is:
Total_Amount__c/ IF(Start_Date__c = End_Date__c, 1, BLANKVALUE( SBQQ__ProrateMultiplier__c, CEILING((((SBQQ__SubscriptionEndDate__c - SBQQ__SubscriptionStartDate__c) / 365) * 12)) / SBQQ__Product__r.SBQQ__SubscriptionTerm__c ) )When the dates are equal (start date of 1/1/2019 and end date of 1/1/2019) the "ARR Contribution 2" field populates with the Total Amount value as expected.
But if I update the dates so that, for example, the start date is 7/1/2018 and the end date is 6/30/2019, I was expecting that the "ARR Contribution 2" field would be completed because the Prorate Multiplier is blank. But what is happening is that it is putting the $5,000 value in the first "ARR Contribution" field. I would expect that at that point both ARR Contribution fields would say $5,000.
See screenshots below:
Start Date 7/1/2018 and End Date 7/1/2018 (equal dates)
Start Date 7/1/2018 and End Date 6/30/2019 (not equal dates)
Why are my results moving between the two fields? What needs to be tweaked in the formula for my "ARR Contribution 2" field. So, that the expected result for either case populates on the second field.
IF(
SBQQ__SubscriptionEndDate__c = SBQQ__SubscriptionStartDate__c, Total_Amount__c,
Total_Amount__c / BLANKVALUE(SBQQ__ProrateMultiplier__c, CEILING((((SBQQ__SubscriptionEndDate__c - SBQQ__SubscriptionStartDate__c) / 365) * 12)) / SBQQ__Product__r.SBQQ__SubscriptionTerm__c )