topic Set analysis formula not working in QlikView

Set analysis formula not working

Tue, 13 Oct 2015 15:58:40 GMT

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] ,

Re: Set analysis formula not working

Clever_Anjos — Tue, 13 Oct 2015 16:00:58 GMT

What this expression is related to Set Analysis?

Re: Set analysis formula not working

Tue, 13 Oct 2015 16:04:43 GMT

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.

Re: Set analysis formula not working

Clever_Anjos — Tue, 13 Oct 2015 16:16:22 GMT

Would you mind sharing your complete script and some data?

Re: Set analysis formula not working

Tue, 13 Oct 2015 16:31:11 GMT

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

Re: Set analysis formula not working

Clever_Anjos — Tue, 13 Oct 2015 16:35:13 GMT

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

Re: Set analysis formula not working

Tue, 13 Oct 2015 16:38:28 GMT

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

Re: Set analysis formula not working

Clever_Anjos — Tue, 13 Oct 2015 16:45:25 GMT

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

Re: Set analysis formula not working

Clever_Anjos — Tue, 13 Oct 2015 16:48:30 GMT

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

Re: Set analysis formula not working

Clever_Anjos — Tue, 13 Oct 2015 16:54:40 GMT

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;