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
jisaac

# field update based on Close Date quarter and year

I cannot use Multi-Currency on this so am looking to update a percent field based on the Close Date Fiscal Quarter and year.

If the Close date is in Q1 09, the percent is 0.81

for Q2 09 it is 0.86

for Q3 09 it is 0.88 and so on.

I have tried using Case but I can only get that to work based on the month. How do I include the year as well?

Any suggestions?

Jane

CASE(MONTH(CloseDate ),
1, 0.81,
2, 0.81,
3, 0.81,
4, 0.86,
5, 0.86,
6, 0.86,
7, 0.88,
8, 0.88,
9, 0.88,
10, 0.88,
11, 0.88,
12, 0.88,
0.0)
Steve :-/

IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=3)),0.81,

IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=6)),0.86,0.88))

Message Edited by Stevemo on 08-05-2009 05:40 PM

Jakester

I don't quite understand why you're using those specific numbers. Assuming it's just a simple business rule, and you have different simple business rules for different years, you could do something like this:

if year(CloseDate,2009 ,CASE(MONTH(CloseDate ), <4, 0.81, <7, 0.86,.88 ,if year(CloseDate,2010 ,yournextcasestatementhere

jisaac
It did not like the Less Than symbols - said it did not recognize them.

So now I am at this point and it is telling me that I have an extra CASE. Ideas?

IF(year(CloseDate,2009))
CASE(MONTH(CloseDate),
1, 0.81,
2, 0.81,
3, 0.81,
4, 0.86,
5, 0.86,
6, 0.86,
7, 0.88,
8, 0.88,
9, 0.88,0.88)
Jakester

Oh- I thought you could use < symbols... dang. Yes, you closed out the If() too early. Try:

IF(year(CloseDate,2009) ,CASE(MONTH(CloseDate) ,1, 0.81 ,2, 0.81 ,3, 0.81 ,4, 0.86 ,5, 0.86 ,6, 0.86 , 0.88 ) ,"00" )

Steve :-/

IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=3)),0.81,

IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=6)),0.86,0.88))

Message Edited by Stevemo on 08-05-2009 05:40 PM
This was selected as the best answer
Steve :-/

**bleep**!  you beat me to it!

Message Edited by Stevemo on 08-05-2009 05:29 PM

"**bleep**!" ?!?!?   WTF?!?!?   apparently the Board Mod has NO sense of humor...

Message Edited by Stevemo on 08-05-2009 05:38 PM
jisaac

Jakester and Steve,

Thanks guys - both your formulas worked but I went with Steve's because it will be easier to maintain over the years.  I am looking down the road for formulas that will stand the test of time as I add more years and Finance changes the exchange rates we use.

Jane