Help with formula 1

[ekta22]

I have to work on this complicated formula and need some help with it.
I have these two columns that look like below (Right now the columns below are not in any table)

[b]Column 1			Coulmn 2[/b]
[b]DD[/b] Daily			001DD000DD
[b]SW[/b] Semi-Weekly	  003DD001DD
[b]M[/b] Monthly			001MM007DD
[b]SA[/b] Semi-Annual	  006MM030DD
[b]A[/b] Annual			001YY060DD
and so on...

I have these 3 fields in my table - Interval_length, Earliest_Date, Scheduled_Date.
I first need to check the data in my table for interval_length and if it matches any value in column 1 then I move to column 2.
So for example if interval_length in my record is M and it matches the 3rd value in column 1, I look at the corresponding value 001MM007DD
in column 2 and use that to do further calculations.
Based on 001MM007DD I need to calculate the next Scheduled_Date, Earliest_Date. First 5 letters, 001MM tells me the next scheduled date.
So if my current scheduled_date is 06/08/05 the next scheduled_date will occur after 1 month i.e. 07/08/05. After calculating this part
the remaining date fields Earliest_Date and Latest_Date are calculated. To calculate these fields we look at the remaining text in
001MM007DD i.e 007DD. So the new earliest_date will be -7 days from the new scheduled date.

So it will look like

Interval_length	Scheduled_Date	Earliest_Date	
M		         06/08/05	      05/28/05	
M		         07/08/05	      07/01/05(after calculation)	

Another example for interval SW and 003DD001DD would be

Interval_length	Scheduled_Date	Earliest_Date	
SW		        06/08/05	     05/28/05	
SW		        06/11/05	     06/10/05

I would appreciate any help.

 

[synapsevampire]

Perhaps I just don't get it, but I would suggest showing data as it is in the table, and what you want as output.

You speak of a scheduled date, although you don't state what this scheduled date is based on, is it the current date, or???

"First 5 letters, 001MM tells me the next scheduled date.
So if my current scheduled_date is 06/08/05 the next scheduled_date will occur after 1 month i.e. 07/08/05."

How did you know that the current scheduled date was 6/8/05?

Anyway, show all of the data required to make the formulas work and someone should sort this out for you easily.

-k

 

[ekta22]

The scheduled date is already in my table. I just made up 6/8/05 as an example. For every record in my table there is a scheduled date and earliest date. I need to use these fields in the table to calculate the new scheduled and earliest date and then compare these new calculated fields with another date field in my table.

So for example if the scheduled date in my table is 02/10/05 and interval is SA. I will use 006MM030DD.

The new scheduled date will be +6 months (006MM)i.e. 08/10/05 and earliest date would be -30 days (030DD) i.e. 01/11/05.

Then I need to check if the new earliest_date < end_date entered by the user.

 

[synapsevampire]

Again, showing what is in the table(s) and demonstrating what needs to be output is key rather than descriptions of data.

To get you part way there, here's some examples of how to parse out the string and use the values to calculate dates:

Adding to a date:

whileprintingrecords;
numbervar AddedAmount;
stringvar Addtype:= {table.field}[4];
Datevar NewDate;
If AddType = "M" then
NewDate:= dateserial(year({table.datefield}),month({table.datefield})+val({table.otherfield}[1 to 3]),day({table.datefield}))
else
If AddType = "D" then
NewDate:= dateserial(year({table.datefield}),month({table.datefield}),day({table.field}))val({table.otherfield}[1 to 3]);
NewDate

This should get you close.

-k


02/10/05 and interval is SA. I will use 006MM030DD

 

[ekta22]

Ok lets say these are the 3 records in my table.

[b]Interval_Length    Scheduled_date   End_date[/b]
M-------------------04/09/05-------06/08/05
M-------------------02/07/05-------03/20/05
DD------------------06/06/05-------06/02/05

M- 001MM007DD
DD-001DD000DD

Output

[b]New Scheduled_Date                New Earliest_Date[/b]
05/09/05(+1 month-001MM)----------05/02/05(-7 days-007DD)
03/08/05(+1 month-001MM)----------03/01/05(-7 days-007DD)
06/07/05(+1 day-001DD)------------06/07/05(-0 days-000DD)

If new earliest_date <= end_date then count records found.
In this case it should output 2.

I don't know if I should create a new table in my SQL database with data for
M- 001MM007DD
DD-001DD000DD
or can it be saved in the formula itself? There are 33 such different intervals.

 

[ekta22]

anyone?

 

[MisterMo]

You can try and break this down in more sizeable chunks

start with defining your wanted fields first
Create a formula like this one

if {Interval_Length} = "M" then
Date (year({Scheduled_date}),month({Scheduled_date})+ 1 , day({Scheduled_date}))
else if {Interval_Length} = “SA” then
Date (year({Scheduled_date}),month({Scheduled_date})+ 6 , day({Scheduled_date}))
else if {Interval_Length} = “D”then
Date (year({Scheduled_date}),month({Scheduled_date}), day({Scheduled_date}) + 1)
else if {Interval_Length} = “SW” then
Date (year({Scheduled_date}),month({Scheduled_date}), day({Scheduled_date}) + 3)

Then you can check if such date is less then the end date.

That's a start


Mo

 

[ekta22]

Thanks for your reply. I will give it a try. Just one other thing. Before checking for the end date I also need to find out the new earliest_date, which is calculated from the new scheduled_date. How should I fit that in the above code?

 

[MisterMo]

You can use the new formula field and take off seven days.

lets say you call your fomula @NewSchedDate

Create another formula @EarliestDate =

{@NewSchedDate} - 7

all these formula fields should go on the detail section.

Mo

 

[ekta22]

Hi Mo,

I did create {@newSchedDate} formula but I am not too sure about @earliestdate formula. New earliest date is dependent on both the interval and new scheduled date.

If interval_length is 'M' - 001MM007DD, we calculate new scheduled date by adding one month and new earliest date will be -7 days from the new scheduled date.

 

[MisterMo]

I need to know what value you want to pass for the different intervals

Mo

 

[ekta22]

There are 33 different intervals. I will list a few below -
DD - 001DD000DD
SW - 003DD001DD
W - 007DD003DD
M - 001MM007DD
Q - 003MM015DD
A - 001YY060DD

If interval length for a record is W, the new scheduled date will be +7 days (007DD) to the scheduled date in the table and new earliest date will be -3 days(003DD) from the new scheduled date.

Let's assume that scheduled date for all records is 05/05/05 so the output should look like

Interval New Scheduled_date New Earliest Date
DD---------05/06/05(+1day-001DD)----05/06/05(-0 days-000DD)
M----------06/05/05(+1mon-001MM)----06/13/05(-7 days-007DD)
A ---------05/05/06(+1yr-001YY)-----03/05/06(-60days-060DD)

and then I need to check if new earliestdate < enddate.

 

[MisterMo]

You could create a NumOfDays formula

like this

numberVar TotDays;
TotDays :=0 ;
if {Interval_Length} = "M" then TotDays :=7 ;
if {Interval_Length} = "A" then TotDays :=60;
if {Interval_Length} = "SW" then TotDays :=1;
if {Interval_Length} = "W" then TotDays :=1;
if {Interval_Length} = "Q" then TotDays :=15;
if {Interval_Length} = "SA" then TotDays :=30
else
TotDays;

this one does not have to be visible on your report

then you create the New Earliest Date Formula

NewEarliestDate
= {@NewSchedDate} - NumOfDays

then you can compare this date with the End_date






Mo

 

[ekta22]

When I run the report it gives me an error saying
"A month number must be between 1 and 12" and highlights this line in the formula

Date (year({pPMPERDET.SCHEDULED_DATE}),month({pPMPERDET.SCHEDULED_DATE})+ 1 , day({pPMPERDET.SCHEDULED_DATE}))

 

[MisterMo]

There must be something wrong with the formula as a whole because I've just tested this line and get no errors
could you post the entire formula.

Mo

 

[ekta22]

Here is what I have

1. {@NewschedDate}- This formula is in the detail section of my report

if {pPMPERDET.INTERVAL_LENGTH} = "DD" then
Date (year({pPMPERDET.SCHEDULED_DATE}),month({pPMPERDET.SCHEDULED_DATE}) , day({pPMPERDET.SCHEDULED_DATE})+ 1)

else if {pPMPERDET.INTERVAL_LENGTH} = "DA" then
Date (year({pPMPERDET.SCHEDULED_DATE}),month({pPMPERDET.SCHEDULED_DATE}) , day({pPMPERDET.SCHEDULED_DATE})+ 1)

else if {pPMPERDET.INTERVAL_LENGTH} = "SW" then
Date (year({pPMPERDET.SCHEDULED_DATE}),month({pPMPERDET.SCHEDULED_DATE}) , day({pPMPERDET.SCHEDULED_DATE})+ 3)

else if {pPMPERDET.INTERVAL_LENGTH} = "M" then
Date (year({pPMPERDET.SCHEDULED_DATE}),month({pPMPERDET.SCHEDULED_DATE}+ 1) , day({pPMPERDET.SCHEDULED_DATE}))

else if {pPMPERDET.INTERVAL_LENGTH} = "5YR" then
Date (year({pPMPERDET.SCHEDULED_DATE}+ 5),month({pPMPERDET.SCHEDULED_DATE}) , day({pPMPERDET.SCHEDULED_DATE})) etc...

2. {@NumOfDays} - This formula is not on the report

numberVar TotDays;
TotDays :=0 ;
if {pPMPERDET.INTERVAL_LENGTH} = "DD"   then TotDays :=0;
if {pPMPERDET.INTERVAL_LENGTH} = "DA"   then TotDays :=0;
if {pPMPERDET.INTERVAL_LENGTH} = "SW"   then TotDays :=1;
if {pPMPERDET.INTERVAL_LENGTH} = "SW4"  then TotDays :=1;
if {pPMPERDET.INTERVAL_LENGTH} = "W"    then TotDays :=3;
if {pPMPERDET.INTERVAL_LENGTH} = "BW"   then TotDays :=3;
if {pPMPERDET.INTERVAL_LENGTH} = "4W"   then TotDays :=7;
if {pPMPERDET.INTERVAL_LENGTH} = "28D"  then TotDays :=7;
if {pPMPERDET.INTERVAL_LENGTH} = "M"    then TotDays :=7;
if {pPMPERDET.INTERVAL_LENGTH} = "6W"   then TotDays :=7;
if {pPMPERDET.INTERVAL_LENGTH} = "42D"  then TotDays :=7;
if {pPMPERDET.INTERVAL_LENGTH} = "BM"   then TotDays :=7;
if {pPMPERDET.INTERVAL_LENGTH} = "Q"    then TotDays :=15;
if {pPMPERDET.INTERVAL_LENGTH} = "84D"  then TotDays :=15;
if {pPMPERDET.INTERVAL_LENGTH} = "4M"   then TotDays :=15;
if {pPMPERDET.INTERVAL_LENGTH} = "SA"   then TotDays :=30;
if {pPMPERDET.INTERVAL_LENGTH} = "168"  then TotDays :=30;
if {pPMPERDET.INTERVAL_LENGTH} = "A"    then TotDays :=60;
if {pPMPERDET.INTERVAL_LENGTH} = "18M"  then TotDays :=45;
if {pPMPERDET.INTERVAL_LENGTH} = "2YR"  then TotDays :=90;
if {pPMPERDET.INTERVAL_LENGTH} = "26M"  then TotDays :=90;
if {pPMPERDET.INTERVAL_LENGTH} = "3YR"  then TotDays :=120;
if {pPMPERDET.INTERVAL_LENGTH} = "4YR"  then TotDays :=120;
if {pPMPERDET.INTERVAL_LENGTH} = "5YR"  then TotDays :=120;
if {pPMPERDET.INTERVAL_LENGTH} = "8YR"  then TotDays :=120;
if {pPMPERDET.INTERVAL_LENGTH} = "10Y"  then TotDays :=120;
if {pPMPERDET.INTERVAL_LENGTH} = "12Y"  then TotDays :=120;
if {pPMPERDET.INTERVAL_LENGTH} = "AR"   then TotDays :=0;
if {pPMPERDET.INTERVAL_LENGTH} = "2KH"  then TotDays :=0;
if {pPMPERDET.INTERVAL_LENGTH} = "4KH"  then TotDays :=0;
if {pPMPERDET.INTERVAL_LENGTH} = "8KH"  then TotDays :=0;
if {pPMPERDET.INTERVAL_LENGTH} = "4CH"  then TotDays :=0;
if {pPMPERDET.INTERVAL_LENGTH} = "18C"  then TotDays :=0

else
TotDays;

3. {@NewEarlistDate} - This formula is in the detail section of my report

{@NewSchedDate} - {@NumOfDays}

4. {@MissedTasks} - This formula is in the detail section of my report. I then right click on it-Insert-Summarize it in one of my group headers.

If ({pPMPERDET.COMPLETION_STATUS} = "O" AND {@NewEarliestDate} <= {?@END_DATE}) OR
   ({pPMPERDET.COMPLETION_STATUS} = "X" AND {pPMPERDET.MAINT_ACTION_CODE} = "N") THEN
{pPMPERDET.NO_TASKS}

 

[Bennie47250]

Won't this formula give you a month of 13 in December?
"else if {pPMPERDET.INTERVAL_LENGTH} = "M" then
Date (year({pPMPERDET.SCHEDULED_DATE}),month({pPMPERDET.SCHEDULED_DATE}+ 1) , day({pPMPERDET.SCHEDULED_DATE}))

I think you need to throw in an IF Then Else to handle this.
Something like if Month = 12 then 1 else month({pPMPERDET.SCHEDULED_DATE}+ 1).

Remember to add 1 to the year when you go to month 1.

 

[MisterMo]

Well spotted Bennie, this is also true for the 6 months

here is a sample

stringVar  IntLenght;
dateVar   MyDate;
IntLenght := {pPMPERDET.INTERVAL_LENGTH};
MyDate := date({pPMPERDET.SCHEDULED_DATE});

if IntLenght = "DD" then
Date (year(MyDate),month(MyDate) , day(MyDate)+ 1)

else if IntLenght = "DA" then
Date (year(MyDate),month(MyDate) , day(MyDate)+ 1)

else if IntLenght = "SW" then
Date (year(MyDate),month(MyDate) , day(MyDate)+ 3)

else if IntLenght = "M" then
(
if month(MyDate+ 1) > 12 then
Date (year(MyDate + 1),month((MyDate+ 1)-12) , day(MyDate))
else
Date (year(MyDate),month(MyDate + 1) , day(MyDate))
)
else if IntLenght = "SA" then
(
if month(MyDate+ 6) > 12 then
Date (year(MyDate + 1),month((MyDate+ 6)-12) , day(MyDate))
else
Date (year(MyDate),month(MyDate + 1) , day(MyDate))
)
else if IntLenght = "5YR" then
Date (year(MyDate+ 5),month(MyDate) , day(MyDate))

I've created two variables to make it easy to be tested by other experts if necessary.

all the best

Mo

 

[MisterMo]

I have modified the code so that it will work for the month and half year. the previous posting will compile but do nothing.

else if IntLenght = "M" then
(
if month(MyDate+ 1) > 12 then
Date (year(MyDate )+ 1,month(MyDate)+ 1 -12 , day(MyDate))
else
Date (year(MyDate),month(MyDate )+ 1 , day(MyDate))
)
else if IntLenght = "SA" then
(
if month(MyDate)+ 6 > 12 then
Date (year(MyDate )+ 1, month(MyDate ) + 6 - 12 , day(MyDate))
else
Date (year(MyDate),month(MyDate )+ 6 , day(MyDate))
)
else if IntLenght = "5Y" then
Date (year(MyDate)+ 5,month(MyDate) , day(MyDate))

Mo

 

[ekta22]

I get the idea. Let me try this out. One more thing, I have couple of intervals where month is +18, +26. How should I take care of that? And do I need to check for days as well. For example if scheduled date is 1/26/05 and I need to add 15 days will the forumula do 26+15 = 41 or will it show 2/10/05?