+ Start a Discussion
SvenBeSvenBe 

Formula error ?

I can´t see what is wrong - can you ?

 

Custom formula field  "Industry Revenue Fit"  combines two fields. Formula

Industry_Fit_Automatic__c & Revenue_fit__c

Gives a combination of letters, i.e. LL, ML, MM 

 

Works fine

 

In next field, I would like to create a figure 1-3, depending on letter combination. I always get the "else value - Error".

 

 

 

CASE( IndustryRevenueFit__c, "LL", "3", "ML", "3","HL", "2", "LM", "3", "MM", "2", "HM", "1","LH", "2", "MH", "1", "HH", "1", "Error")

 

Is it so, that I can not include a custom formula field in this case formula ?  Or, do you see anything else wrong ?

 

Greetings !

hhuiehhuie

Tested your code and it works.  Are letters in the two fields always capitalized? (It's case sensitive)

 

Only way you can troubleshoot this is to display the Industry Revenue Fit field to see what's in the field.

SvenBeSvenBe

Thanks for testing. I am trying to automate the rating process of our prospects / customers, and I just get stuck. It is one of the last things in an improvementproject I would like to close...

 

Here is the whole scenario - the end result shall be i.e.   1HH (1 for total rating, High Industry rating, High Revenue rating).

 

 

1. In customfield "Industry", user chooses industry code from a picklist (the options are listed in the formula below)

 

1:2 Custom text formula field "Industry Fit" gives L, M, H according to this formula

 

CASE( Industry , "03 Construction","H","10 Electronics & Instruments","H","12 Logistic & transportation","H","14 Telecom","H","18 Finance, Insurances & Banking","H","21 Business Services","H","04 CPG","M","06 Media","M", "07 Chemical & Pharma Industry","M", "09 Manufacturing & Machinery Industry","M", "11 Vehicles (incl. Automotive)","M", "15 Energy & Utilities","M", "17 Retail","M", "22 Healthcare","M", "01 Wood, Timber, Glass, Cement","L", "02 Mining, Oil & Gas","L", "05 Paper & Pulp","L", "08 Metal Industry","L", "13 Touristic","L", "16 Wholesales","L", "19 Real Estate & FM","L", "20 Holdings & Investors","L", "24 Other","L", "None")

 

 

[works fine]

 

 

2. Standard field Annual Revenue is filled in.

 

2:1 Custom text formula field "Revenue Fit" gives L, M, H according to this formula 

 

CASE( IndustryRevenueFit__c , "LL", "3", "ML", "3","HL","2", "LM", "3", "MM", "2", "HM", "1","LH","2", "MH","1", "HH", "1", "Error")

 

 

[This does not function - I only get i.e    ErrorML, ErrorMM - hence, always the else value.]

 

 (Question - is it possible to include a formula field (IndustryRevenueFit above) in the case formula above? Can this be the error - or is the formulas simply to big? It is possible to save them all - I had problems with compiling formula size in the beginning, but that has disappeard.

 

 

[[ I now rebuilt the field - and then got back to the compiling formula problem. It works if I only have a few "ifs" i.e. up to 3 values - but, above that it gets over 5000 (limit). The whole line adds up to 24000 - Probably thats why I got the Error (elsevalue) before ]]

 

5. Custom formula field "Account summary" gives i.e. 2HM according to the formula field below 

 

 

Account_Prio_Category_Automatic__c & Industry_Revenuefit__c

 

 

[Formula works, - it combines the two fields, but only, as explained above, it only returns the else value from field "Account_Prio_Category_Automatic_c    i.e.  ErrorHM]

 

  

6. The combination of figure, letter, makes it possible for me to extract a matrixreport where I can see how many accounts I have in the different categories - hence, we can prioritize our resources.

 

 

Numbers of 2HL

Numbers of 1HM

Numbers of 1HH

and so on... In a beautiful matrix and a dashboard....

 

 

Conclusion

 

1              Industry                                 chose industrycode from picklist

1:1          Industryfit                              is set to H, M, L

2              Annual revenue                      enter annual revenue in numbers

2:1          Revenuefit                              is set to H, M, L

3              Industry & Revenuefit             is set to a combination of letters (i.e MH) from 1:1 and 2:2

4              Account Prio Category Autom.         A number is set (1,2,3) depending on lettercombination in 3

5              Account summary               Account Prio Category autom. & Industry & Revenuefit i.e 2MM

6              I can create reports.

Message Edited by SvenBe on 03-05-2009 07:07 AM