Set analysis formula not working

[#1]

This is the present calculation.  If a completed K04 or 367 occurs 15 days or less in the month, increment 1 month and use the first day of that month, else increment by 2 months

For example K04 sent 8/15/15, increment to the first day of next month or 9/1/15, else increment 2 months to 10/1/15

//load
//
//If(len(trim([K04 Date]))<>0 ,
//     (if(day([K04 Date])<=15,date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],2))))),
//                if([LM Workout Status Act]='IN TRIAL',[K04 Date],
//            If(len(trim([367]))<>0 ,
//                   (if(day([367])<=15,date(monthstart(addmonths([367],1))),date(monthstart(addmonths([367],2))))),
//                if([LM Workout Status Act]='IN TRIAL',[367]
//                 )))) as [trial1_due_dt] ,

Now I need to re-work the calculation to say if the investor is FHA and the completed K04 or 367 occurs less than 21 days into the month increment by 1 month, else increment by 3 months.  I used this formula,  Do you see any modifications needed.  I am not getting desired results. In some cases the dates are not incrementing.

load  /*add 10/6/15 to update trial due date for special fha requirements*/
If(len(trim([K04 Date]))<>0 and match([D Investor],'FHA') ,
      (if(day([K04 Date])<21,date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],3))))),
                 if([LM Workout Status Act]='IN TRIAL',[K04 Date],
               If(len(trim([367]))<>0 and match([D Investor],'FHA'),
               (if(day([367])<21,date(monthstart(addmonths([367],1))),date(monthstart(addmonths([367],3))))),
                 if([LM Workout Status Act]='IN TRIAL',[367] ,

[Clever_Anjos]

What this expression is related to Set Analysis?

[#3]

I may not be using the correct terminology.  I am attempting to get a date to increment based on a date range completion of a K04 or 367.  These are letter dates.  The one in green worked good.  I simply added a condition in the one in black that said if the investor was FHA and the dates occurred on a date less than 21 days into the month, increment the date by 1 month, else increment by 3 months.

[Clever_Anjos]

Would you mind sharing your complete script and some data?

[#5]

load   If(len(trim([K04 Date]))<>0 and match([D Investor],'FHA') ,
(if(day([K04 Date])<=22,date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],3))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0 and match([D Investor],'FHA'),
(if(day([367])<=22,date(monthstart(addmonths([367],1))),date(monthstart(addmonths([367],3))))),
if([LM Workout Status Act]='IN TRIAL',[367] ,


If(len(trim([K04 Date]))<>0 and not match([D Investor],'FHA') ,
(if(day([K04 Date])<=15,date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],2))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0 and not match([D Investor],'FHA') ,
(if(day([367])<=15,date(monthstart(addmonths([367],1))),date(monthstart(addmonths([367],2))))),
if([LM Workout Status Act]='IN TRIAL',[367]
)))))))) as [trial1_due_dt] ,


If(len(trim([K04 Date]))<>0 and match([D Investor],'FHA') ,
(if(day([K04 Date])<=22,date(monthstart(addmonths([K04 Date],2))),date(monthstart(addmonths([K04 Date],4))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0 and match([D Investor],'FHA') ,
(if(day([367])<=22,date(monthstart(addmonths([367],2))),date(monthstart(addmonths([367],4))))),
if([LM Workout Status Act]='IN TRIAL',[367] ,


If(len(trim([K04 Date]))<>0 and not match([D Investor],'FHA') ,
(if(day([K04 Date])<=15,date(monthstart(addmonths([K04 Date],2))),date(monthstart(addmonths([K04 Date],3))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0 and not match([D Investor],'FHA') ,
(if(day([367])<=15,date(monthstart(addmonths([367],2))),date(monthstart(addmonths([367],3))))),
if([LM Workout Status Act]='IN TRIAL',[367]
)))))))) as [trial2_due_dt] ,


If(len(trim([K04 Date]))<>0 and match([D Investor],'FHA') ,
(if(day([K04 Date])<=22,date(monthstart(addmonths([K04 Date],3))),date(monthstart(addmonths([K04 Date],5))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0 and match([D Investor],'FHA'),
(if(day([367])<=22,date(monthstart(addmonths([367],3))),date(monthstart(addmonths([367],5))))),
if([LM Workout Status Act]='IN TRIAL',[367] ,


If(len(trim([K04 Date]))<>0 and not match([D Investor],'FHA') ,
(if(day([K04 Date])<=15,date(monthstart(addmonths([K04 Date],3))),date(monthstart(addmonths([K04 Date],4))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0 and not match([D Investor],'FHA') ,
(if(day([367])<=15,date(monthstart(addmonths([367],3))),date(monthstart(addmonths([367],4))))),
if([LM Workout Status Act]='IN TRIAL',[367]
)))))))) as [trial3_due_dt] ,


If(len(trim([K04 Date]))<>0 and match([D Investor],'FHA') ,
(if(day([K04 Date])<=22,date(monthstart(addmonths([K04 Date],4))),date(monthstart(addmonths([K04 Date],6))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0 and match([D Investor],'FHA') ,
(if(day([367])<=22,date(monthstart(addmonths([367],4))),date(monthstart(addmonths([367],6))))),
if([LM Workout Status Act]='IN TRIAL',[367] ,


If(len(trim([K04 Date]))<>0 and not match([D Investor],'FHA') ,
(if(day([K04 Date])<=15,date(monthstart(addmonths([K04 Date],4))),date(monthstart(addmonths([K04 Date],5))))),
if([LM Workout Status Act]='IN TRIAL',[K04 Date],
If(len(trim([367]))<>0 and not match([D Investor],'FHA'),
(if(day([367])<=15,date(monthstart(addmonths([367],4))),date(monthstart(addmonths([367],5))))),
if([LM Workout Status Act]='IN TRIAL',[367]
)))))))) as [trial4_due_dt] ,

*
;

Loan Number	Investor	Trial Sent Date	1st Trial  Due Date	2nd Trial  Due Date	3rd Trial  Due Date	4th Trial  Due Date
1111111	FHA	05/04/2015	06/01/2015	07/01/2015	08/01/2015	09/01/2015
3333333	FHA	07/24/2015	09/01/2015	10/01/2015	11/01/2015	12/01/2015
444444	FNMA	08/14/2015	09/01/2015	10/01/2015	11/01/2015	12/01/2015

So in this example, presently if trial sent occurs prior to the 15^th of the month increment the 1^st trial due date by 1 month, else increment 2 months (see loan 33333 as example)

The above code now adds another condition and says that if the trial sent date (K04 or 367) occurred more than 21 days into the month and if the investor is 'FHA increment by 3 months, else keep the same logic as the first sentence

[Clever_Anjos]

That is the final table, which one is the source?

[#7]

Its a pretty long program Ill have to get back to you on this.  Also I will check syntax, that may be the issue

[Clever_Anjos]

Anyway, it´s a very long boolean expression, wondering if it is possible to rewrite it using a more simple logic

[Clever_Anjos]

For example, you test [LM Workout Status Act]='IN TRIAL' 4 times into that expression, perhaps testing at first, could simplify your code

[Clever_Anjos · Accepted solution]

You can use preceding load too to calculate intermediate values that simplify your code

Example

load *,
  if(day(Base)<=15,date(monthstart(addmonths([367],6))));               
load *,
  if(len(trim([K04 Date]))<>0,[K04 Date],[367]) as Base; // intermediate date
load
  date(Today() - RecNo()) AS [K04 Date],
  date(Today() - RecNo() + mod(RecNo(),10)) AS [367], 
  Pick(mod(RecNo(),2)+1,'FHA','Other') as [D Investor],
  Pick(mod(RecNo(),2)+1,'IN TRIAL','Other') as [LM Workout Status Act]     
AutoGenerate 1000;