+ Start a Discussion
GMASJGMASJ 

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
  
 
 

Best Answer chosen by GMASJ
shephalishephali
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

All Answers

Bryan JamesBryan 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


 
shephalishephali
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
shephalishephali
Please dont forget to mark it as solution if you get your answer.
GMASJGMASJ
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
shephalishephali
It wont work as picklist value must be first converted to text .