Sign Up ›
  • Datajunkie
  • NEWBIE
  • 0 Points
  • Member since 2008
  • Chatter
    Feed
  • 0
    Best Answers
  • 1
    Likes Received
  • 0
    Likes Given
  • 8
    Questions
  • 5
    Replies
3 replies

Need Help with YTD Quota Formula

I need a custom formula field in the User object to calculate YTD Quota per person. So far, every formula I try runs into the "No Formula >5,000 Characters" rule, or it's so simple that too much falls through the cracks. Can anyone think of a way to simplify?

 

The rules are deceptively simple...

 

1) Each rep can have a different annual quota, split into a monthly goal.

2) Each rep gets two months "free" after their hire date before quotas start accumulating. If hired after the 15th of the month, they get that month "free" too.

3) Our fiscal year goes from 2/1 to 1/31

4) I've been determining the current fiscal month being reported by extracting the month from today's date,  and if it's January, setting the fiscal month # to 11 (December), or if it's February, setting the fiscal month # to 12, otherwise taking the month number - 2.

 

I've been approaching this by trying to capture the date the quota starts, then using that to determine where in the fiscal year this falls, and then determine how many months they get "free" that year versus the number of months elapsed so far this year.  But like I said, that's running into the character limit. Ultimately, I've been trying to find a way to capture the # of months I should multiply by the monthly quota (bold, italic, underlined below). But if someone can think of another way to go about it, I'm all ears.

 

I'm probably missing something terribly obvious, so I'm hoping a fresh set of eyes will see something that I'm overlooking because I'm trying to be too complicated.

 

Example:

 

Rep A

Hire Date  4/1/06

Date Quota Starts  6/1/06 (far prior to current FY)

Annual Quota  $500,000

Monthly Quota  $41,666.67

Current Reporting Month:  November 2009, which is fiscal month #10 of the current year

YTD Quota should be $416,667 or Monthly Quota *10 (same as fiscal month #)

 

Rep B

Hire Date  5/1/09

Date Quota Starts  7/1/09

Annual Quota  $600,000

Monthly Quota  $50,000

Current Reporting Month - November, fiscal month #10

YTD Quota should be $500,000 or Monthly Quota *5 (this is where I get complicated, trying to capture the month of the Date Quota Started and adjusting to the fiscal month by -2 if it's March-December, or forcing if it's Jan or Feb)

 

Rep C

Hire Date 11/28/09

Date Quota Starts  1/28/10

Annual Quota $480,000

Monthly Quota $40,000

Current Reporting Month - November, fiscal month #10

YTD Quota should be $0 or Monthly Quota * 0 , since the Date Quota Starts > the start of the current fiscal month  (and this is where I've totally failed, since this goes into another year, and the month has to be forced because it's calendar month #1, but fiscal month #12)

 

Ideas? Suggestions? Anything? I'm desperate!

2 replies

Formula field that can lookup values from another table?

I'm trying to determine if something is possible, and before I tell management it's not, I wanted to make sure that there wasn't any other way to get around this.
 
We are buying a cross-reference database that shows every valid Zip Code in the US and matches them up to a Metropolitan Statistical Area, as well as the County. Management wants to have a field for County and MSA in the Account record that pulls the values from this database.
 
i.e. if the Account has a ShippingPostalCode of 30004, he wants the County field to automatically be filled in with "Fulton" and the MSA field to be filled in with "Atlanta" (which comes from this database).
 
I can get the data from the database into a custom object in Salesforce, but how do I match up the ShippingPostalCode to this custom object so that the formula field will automatically populate the County and MSA fields?
 
It sounds like the VLOOKUP formula is exactly what I need, but it only works in validation rules - which will do me no good at all.
 
For obvious reasons, I can't write a workflow rule for every possible zip code in the US and then tie it to a field update that will fill in the County and MSA, but it sounds like that's the only way to do this.
 
Please tell me I'm wrong! Is there some other way to do this?
3 replies

VLOOKUP problems: field doesn't exist.

Hello
As a solution to a question I posted the other day (http://community.salesforce.com/sforce/board/message?board.id=general_development&thread.id=23809) someone suggested using a VLOOKUP (which is a brilliant idea :smileyhappy:)

Now I've been brave and set up a custom object with two fields in it:

Custom object = Membership_Category_for_Lookup (that's the object name)
Fields = Base_Fee and Membership_Category

So looking at the help section, I thought my VLOOKUP formula should be:

VLOOKUP($ObjectType.Membership_Category_for_Lookup.Fields.Base_Fee,
$ObjectType.Membership_Category_for_Lookup.Fields.Name,
 Membership_Category__c )

But I'm getting an error that says the field $ObjectType.Membership_Category_for_Lookup.Fields.Base_Fee doesn't exist.

I'm a bit stumped. I've tried several things like using the API name and so on, but no luck.

Anyone got any suggestions?

Thanks
Nicole
3 replies

Need formula that will multiply a field in a related list with a field in the parent

Long story shortened...
 
Our Opportunities are often shared sales with other reps. The Sales Team related list is nice to capture who they are, but that's all it can do. I need to capture not only who they are, but what percent of the sale they get credit for, what that equates to in terms of "volume credit", the commission amount paid to each, and the date paid.
 
The end product will simply be a report that shows the total dollars sold for each rep - which will sometimes include a partial credit sale. Example:
 
Jane Smith sold one Opp by herself, for $100 (is listed as the owner of the Opp)
John Doe sold one Opp by himself, for $200 (is listed as the owner of the Opp)
Jane and John cooperated on another Opp, for $100. Jane is the owner of the Opp, and for this sale, she should receive 75% credit, with John to get 25%
 
The report needs to show:
 
Jane Smith  $175
John Doe   $225
 
Sounds really simple, but is driving me up the wall.
 
I tried creating multiple fields on the Opportunity object, which worked just fine. The data entry would be easy, and the display of information very easy to understand. But then I can't report on it in a useful way, because I can't combine the Amount field with the two Shared Volume fields I created, and match up the rep at the same time. I think the right thing to do is to have a related list, similar to that Sales Team list, but with more fields.
 
I created a custom object - Shared_Sales, with a master-detail relationship to Opportunity. Then I created the fields for Rep, Percent Credit, Commission Paid, and Commission Date. But the "Volume Credit" field should be a calculation of the Opportunity Amount times the Percent Credit (my custom field in the custom object). I can't seem to make the formula "reach back" to the Opportunity record to capture the Amount.
 
I made an S-Control which can do it, but I can't make that S Control show up in the related list on the Opportunity page. Having just the name and percent is good, but they're going to want to see the exact $ volume that they'll receive credit for.
 
That's not even to mention the problem of trying to figure out how to populate the related list with a default record if there are no additional reps. (i.e. - the Owner needs to get 100% credit if there is no one else involved, so I can report on the "Volume Credit" field as a whole).
 
If I'm totally barking up the wrong tree, please tell me! This seems like such a simple function, and management is absolutely adamant that we *must* be able to do this. I've already spent 2 days on this, and I'm no closer to a solution than when I started.
 
HELP!
3 replies

Duplicating Owner Name (not ownerID) on a page

I need to be able to repeat the name of the Opportunity Owner in a second section of the page, but so far, the only field I can merge with is OwnerID - and a string of random letters and numbers isn't exactly what I'm looking for.
 
The idea is that we have "shared sales" where two or three people share credit for a particular sale. We need to store their names and the percentages of credit for each one. I've added fields for the extra two reps (lookups to Users), and for their percentages (defaulting to 0%). But to make things crystal clear, I want the owner's name to appear in the section too - defaulted to 100%, but with a validation rule that makes sure the sum of all 3 percent fields = 100%. Obviously, I don't want them changing the duplicate Owner field. I'm hoping I can make it read-only. But I do want it to show up to make it absolutely clear who has what percentage credit.
 
Is there a way to merge my duplicate field with the Owner's name instead of the ID? It's obviously a two-step link - it starts with the OwnerID on the current object, but then has to link to the Users Object to grab the associated Name. Fancy formula work, maybe?