+ Start a Discussion
MikeyOMikeyO 

Freeze or Lock a Field Info

I have a formula field that gives me a Avg. Revenue Per User (ARPU). This is done every month beginning in January and ending in December.

It divides total monthly revenue / total number of subscriptions.

So for instance.

On January 30 the account billed $100 and had 100 subscriptions : ARPU = $1

If I don't lock this formula field, once I add the next month subscription the ARPU for January would change. i.e.

 

On January 30 the account billed $100 and had 100 subscriptions : ARPU = $1

On February 28 the account billed $110 and had 110 subscriptions : ARPU = $1

but then January would calculate wrong,

January would look at the $100 billed in January and divide by 110 subscriptions (the new total number subscriptions): ARPU = $0.91

and this ARPU number would continue to decline as the subscriptions grow.

This is caused by the subscription number changing from month to month.

 

Any ideas on this?

Satish_SFDCSatish_SFDC
Because historical data has to be stored, a formula field is not suitable for this type of requirement. Formula field dynamically calculates the data and changes with every change of its parameters.

Are there different formula fields for different months or just one formula field?

What if you create another child custom object to store monthly Avg's. This way you will have historical data and can even roll up the total average.

May be we could get to better solutions if you give us some more information. Like the different objects involved. Where is Subscriptions data stored etc.

Regards,
Satish Kumar
MikeyOMikeyO

They are different formulas for different months

(October_Total_Revenue__c )/ ( Oct_Cumulative_Subs__c+ Opportunity__r.Account.Active_Subscriptions__c )

 

I was trying to stay away from the archive solution, since we only have one field that captures "Total Active Subscriptions"

Otherwise, I'll get into an ongoing field that needs to be created every month for every year for the foreseable future.

Satish_SFDCSatish_SFDC
Ok. So archiving is out.
Formula fields being dynamically calculated, always depend on the current
value of the Subscriptions field.
Workflows are also out as you cannot update child object records from
parent.
Triggers? What if you make those individual monthly fields, normal currency
type fields (read-only for all profiles) and have it update every month or
whenever the subscriptions data in account is changed. The trigger might
have code to only update the child object monthly avg. fields for the
current month only.

Regards,
Satish Kumar