You need to sign in to do that
Don't have an account?
Matthew Loh
Multiple IF AND picklist conditions for a formula
Hi all.
Firstly Merry Christmas! :)
My org does basically all of our sales from Partners, and each partner has a different pricing strategy. Within each partner there are also 2 types of services, so really for one partner we are talking about 2 different values which must be calculated. (E.g. Partner 1 AND Service 1, Partner 1 AND Service 2, Partner 2 AND Service 1, etc...)
Both Partner__c and Service__c are restricted picklists (hence why I used TEXT(picklist_field__c) = "blablabla" below.
The expected revenue for each partner would always be based upon 2 custom currency fields, its
I'd like to be able to generate forecasts/dashboard based on expected revenue of each opportunity.
After finding a similar post and using the best-selected answer here https://success.salesforce.com/answers?id=90630000000h1saAAA
I have come up with:
I'm using a simplified version of the expression that needs to be calculated because it's actually going to be something like annual_turnover__c * 0.2 + annual_transactions * 0.1
But that's simple to implement once I get the actual formula right with the IF AND conditions.
Any ideas on what I'm doing wrong? This is doing my head in on Christmas eve haha :)
I considered using CASE for this formula, but I read in that post I linked above that you can't use Nested IF statements within a CASE Function.
Thanks guys and Merry Christmas!!! :)
Firstly Merry Christmas! :)
My org does basically all of our sales from Partners, and each partner has a different pricing strategy. Within each partner there are also 2 types of services, so really for one partner we are talking about 2 different values which must be calculated. (E.g. Partner 1 AND Service 1, Partner 1 AND Service 2, Partner 2 AND Service 1, etc...)
Both Partner__c and Service__c are restricted picklists (hence why I used TEXT(picklist_field__c) = "blablabla" below.
The expected revenue for each partner would always be based upon 2 custom currency fields, its
I'd like to be able to generate forecasts/dashboard based on expected revenue of each opportunity.
After finding a similar post and using the best-selected answer here https://success.salesforce.com/answers?id=90630000000h1saAAA
I have come up with:
IF(AND(TEXT(Partner__c) = "Partner 1",TEXT(Service__c) = "Service 1", Annual_turnover__c * 0.1, IF(AND(TEXT(Partner__c) = "Partner 2",TEXT(Service__c) = "Service 2", Annual_turnover__c * 0.2, 0))but I keep getting Syntax errors (e.g. extra ")", and when I remove that it becomes extra ",", etc. etc.)
I'm using a simplified version of the expression that needs to be calculated because it's actually going to be something like annual_turnover__c * 0.2 + annual_transactions * 0.1
But that's simple to implement once I get the actual formula right with the IF AND conditions.
Any ideas on what I'm doing wrong? This is doing my head in on Christmas eve haha :)
I considered using CASE for this formula, but I read in that post I linked above that you can't use Nested IF statements within a CASE Function.
Thanks guys and Merry Christmas!!! :)
You just forgot to close your AND conditions (after "Service 1" and 2).
IF(
AND ( TEXT(Partner__c) = "Partner 1" , TEXT(Service__c) = "Service 1" ), Annual_turnover__c * 0.1 ,
IF(
AND ( TEXT(Partner__c) = "Partner 2" , TEXT(Service__c) = "Service 2" ), Annual_turnover__c * 0.2 , 0
)
)
Merry Christmas.
Alain
All Answers
You just forgot to close your AND conditions (after "Service 1" and 2).
IF(
AND ( TEXT(Partner__c) = "Partner 1" , TEXT(Service__c) = "Service 1" ), Annual_turnover__c * 0.1 ,
IF(
AND ( TEXT(Partner__c) = "Partner 2" , TEXT(Service__c) = "Service 2" ), Annual_turnover__c * 0.2 , 0
)
)
Merry Christmas.
Alain
Thank you so much!!!!!!!
You've made my day :)
Merry Christmas!!!
Now I can start working on the more challenging stuff like average opportunity stage close time haha
I need 3 different output based on Account tiers and Metric tiers:
- if Account Tier contains Premier and Metric Tier contains Premier -> OK
- if Account Tier contains Premier and Metric Tier contains Advanced or Standard -> NOT OK
- if Account Tier contains Advanced or Standard and Metric Tier contains Premier -> SUPER
Unfortunately SUPER applies to Account tiers with same values as Metric tiers. how can I solve this?
This is the fomula I used:
IF (CONTAINS(FK_Account.Tier_Formula__c, "Premier"),
IF ( AND (CPP_Metric__c.Tier__c = "Premier"),
IF ( OR ( CPP_Metric__c.Tier__c = "Standard",
CPP_Metric__c.Tier__c = "Advanced"
),
"Teste OK",
"OK"
),
"NOT OK"
),
"SUPER"
)