Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
GMASJ

# Formula Calculation

Hi

We have a requirement to write a formula field for calulating below details are mentioned as follows.

1. Unit of Measure ( Which is pick list holds Year/Month/Days)
2. List Price
3. Quantity
4. Subscription Term

Here Subscription Term is dependent on Unit of Measure
if Unit of Measure  = Year it will populate ( 1 to 5)
if Unit of Measure  = Month it will populate ( 1 to 12)
if Unit of Measure  = Days it will populate ( 1 to 31)

Based on the above value I need to build a formula field

IF Unit of Measure = Year = ( List Price * Year * Quantity )

IF Unit of Measure = Month = ( List Price / 12(Months in year)  * Quantity )

IF Unit of Measure = Days = ( List Price / 365(Days in year) * Quantity )

Please suggest me how to get this formula

Thanks

Sudhir

shephali
Hi Sudhir Narayanaswamy,
Using the field depency yuo can easily get the following :
Here Subscription Term is dependent on Unit of Measure
if Unit of Measure  = Year it will populate ( 1 to 5)
if Unit of Measure  = Month it will populate ( 1 to 12)
if Unit of Measure  = Days it will populate ( 1 to 31) .
After that try the below formula to calculate :
```IF(ISPICKVAL(unit_of_measure__c, "years"),(list_price__c  *  VALUE(TEXT(subscription_term__c))*  quantity__c ), IF(ISPICKVAL(unit_of_measure__c, "months"),(list_price__c  / VALUE(TEXT(subscription_term__c))*quantity__c),
IF(ISPICKVAL(unit_of_measure__c, "days"),(list_price__c  / VALUE(TEXT(subscription_term__c))  *  quantity__c) , 00000.00) ) )```

Thanks
shephali

Bryan James
Hey Sudhir,
This should be able to be accomplished using a CASE in the advanced formula editor.
You can use this link to see how Salesforce likes to use them.
`https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_a_h.htm&language=en_US#CASE`

shephali
Hi Sudhir Narayanaswamy,
Using the field depency yuo can easily get the following :
Here Subscription Term is dependent on Unit of Measure
if Unit of Measure  = Year it will populate ( 1 to 5)
if Unit of Measure  = Month it will populate ( 1 to 12)
if Unit of Measure  = Days it will populate ( 1 to 31) .
After that try the below formula to calculate :
```IF(ISPICKVAL(unit_of_measure__c, "years"),(list_price__c  *  VALUE(TEXT(subscription_term__c))*  quantity__c ), IF(ISPICKVAL(unit_of_measure__c, "months"),(list_price__c  / VALUE(TEXT(subscription_term__c))*quantity__c),
IF(ISPICKVAL(unit_of_measure__c, "days"),(list_price__c  / VALUE(TEXT(subscription_term__c))  *  quantity__c) , 00000.00) ) )```

Thanks
shephali
This was selected as the best answer
shephali
GMASJ
Thanks Shephali for you reply Just want to know I tried below usign case does his have any issue let me know.
```CASE(1,
IF(ISPICKVAL (cldy_uom__c, "Year(s)"),1,0),
(( UnitPrice * Discount ) * VALUE(Subscription_Term_Formula__c) *  Quantity ),
IF(ISPICKVAL (cldy_uom__c, "Month(s)"),1,0),
(( (UnitPrice / 12) * Discount ) * VALUE(Subscription_Term_Formula__c) *  Quantity ),
IF(ISPICKVAL (cldy_uom__c, "Day(s)"),1,0),
(( (UnitPrice / 365) * Discount ) * VALUE(Subscription_Term_Formula__c) *  Quantity ),
0
)```

Thanks
Sudhir
shephali
It wont work as picklist value must be first converted to text .