Formula Field based on Subscription Start and End Dates in Salesforce Not Responding as Expected

Question

We have a formula field on the Subscription record called "ARR Contribution". The formula is:

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 (to maintain the integrity if the original "ARR Contribution" field. The revised formula is:

Total_Amount__c/ IF(SBQQ__SubscriptionStartDate__c = SBQQ__SubscriptionEndDate__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 as well as the "ARR Contribution" field would be completed. 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.

Here are screenshots of the results on an actual subscription record:

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)

My first question is - why are the results moving from one field to the other? The formula obviously needs to be tweaked. So, I am not sure what to tweak in the current ARR Contribution 2 formula field.

But I was also trying the following formula instead but it is giving my an error about "Error: Syntax error. Extra ","" and highlights the comma after the >1 information:

Total_Amount__c/ ABS(SBQQ__SubscriptionStartDate__c - SBQQ__SubscriptionEndDate__c) >1, BLANKVALUE( SBQQ__ProrateMultiplier__c, CEILING((((SBQQ__SubscriptionEndDate__c - SBQQ__SubscriptionStartDate__c) / 365) * 12)) / SBQQ__Product__r.SBQQ__SubscriptionTerm__c ) )

Answer 1

EDITED 11/18/19 10:15 am PST

That just means it is not being resolved as it was in the initial field due to the insertion of IF and BLANKVALUE. At this point it might be worth specifying duplicate logic to try and get the calculation done. My next step would be to have the IF statement as the outtermost element and specify everything for each outcome. This will make your formula longer which is non desired but might be necessary for this use case.

IF(SBQQ__SubscriptionStartDate__c == SBQQ__SubscriptionEndDate__c,
Total_Amount__c/BLANKVALUE(SBQQ__ProrateMultiplier__c,1), 
Total_Amount__c/BLANKVALUE(SBQQ__ProrateMultiplier__c,
CEILING((((SBQQ__SubscriptionEndDate__c - SBQQ__SubscriptionStartDate__c) / 365) * 12)) / SBQQ__Product__r.SBQQ__SubscriptionTerm__c ))

OLD RESPONSE

Try nesting the IF in the BLANKVALUE instead of the other way around (this would be my first thought as it seems that the BLANKVALUE isn't resolving when inside the IF)

Total_Amount__c / 
BLANKVALUE( 
    SBQQ__ProrateMultiplier__c, 
    IF(SBQQ__SubscriptionStartDate__c = SBQQ__SubscriptionEndDate__c, 
        1, 
        CEILING((((SBQQ__SubscriptionEndDate__c - SBQQ__SubscriptionStartDate__c) / 365) * 12)) / SBQQ__Product__r.SBQQ__SubscriptionTerm__c ))