Help with a formula field 8.5

[afelya]

Here is what i am trying to accomplish

Marketing and Sales Revenue Forecast Pipeline Date Report
SBU J2 F2 M2 A2 M2 J2 J2 A2 S2 O2 N2 D2 02T 02Q1 02Q2 02Q3
1 2 3 4 5 6 7 8 9 10 11 12 Sum(1-12)
Sum(1-3)
Sum(4-6)
Sum(7-9)

02Q4 03T 04T 05T 06T
Sum(10-12)
Sum(13-24)
Sum(25-36)
Sum(37-48)
Sum(49-60)

Above info will be populated from below metrix
LeadID 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 To 60
$ $ $ $ $ $.....
Calculate monthly revenue for each lead = Net Rev/(Duration*12) <--This is done

If Duration is blank then the following rules apply:
If Net Revenue < $1M, then Duration = 1 year
If Net Revenue = $1-10M, then Duration = 2 years
If Net Revenue > $10M, then Duration = 5 years <--This is done

Then insert monthly revenue in the cell corresponding to 1 + the month of award date. For example, if the award date was June 2002, then the first cell filled would be # 7.

Each cell following the initial cell would also be filled with the monthly revenue for Duration*12 cells, so if the duration was 1 year and the award date was June 2002, the monthly revenue would go into cells 7-18 this would take the amounths respectively in the top first metrix and total accordingly. Say the award month was feb02 then the 3rd cell will get the first monthly amount then if the duration is 1 year then this montly amonth will be entered in each month till cell 15 (12 months) the 2002 total would be (if say 100$) 100*10 months and 2003 total would be 100*3

I have set the report up where i can now have a monthly amounth showing accordingly wiht the award exp month but i can not get these monthly amounth distrubuted so it can show on the following cells to reflect to top metrix depending on duration*12??? Pls help...








Down to some number…so we can fit at least 500 leads

 

[KimCoe]

I would add fields to capture the monthly amount for each column on the matrix by testing the relationship between number of months elapsed from the month of the award date and the matrix column against the duration * 12 value.

If the number of months elapsed is greater than 0 and less than or equal to the duration * 12, the value of the field is the monthly revenue amount, else it is zero.

I don't know of a shortcut to avoid building the formulas for the matrix using some kind of conditional logic.

 

[afelya]

Here is the formula i created for each cell up to number 61. This formula also shows in the first table as the monthly amounths.

Local NumberVar x;

if {BID_SUBJ.AWARD_EXP_DATE} in [Date (2002, 07, 01) to Date (2002, 07, 31)]
then x := {@MonthlyRev}

This as you know only lists the amounth if the award exp date is in 7th month so the aug8 cell and the A2 block gets this amounth yet all the other blocks are 0 since they are not associated with the duration time. Now can you look at this and explain what you were trying to tell me as far as elasped times with award dates please. Thnak you...