Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2015-10-13 11:58 AM

Set analysis formula not working

 

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

 

855 Views
0 Likes
1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee
‎2015-10-13 12:54 PM

In response to Clever_Anjos

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;

View solution in original post

753 Views
9 Replies
Clever_Anjos
Employee
Employee
‎2015-10-13 12:00 PM

What this expression is related to Set Analysis?

753 Views
Not applicable
‎2015-10-13 12:04 PM
Author

In response to Clever_Anjos

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.

753 Views
0 Likes
Clever_Anjos
Employee
Employee
‎2015-10-13 12:16 PM

Would you mind sharing your complete script and some data?

753 Views
0 Likes
Not applicable
‎2015-10-13 12:31 PM
Author

In response to Clever_Anjos

 

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 15th of the month increment the 1st 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

 

753 Views
0 Likes
Clever_Anjos
Employee
Employee
‎2015-10-13 12:35 PM

In response to

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

753 Views
0 Likes
Not applicable
‎2015-10-13 12:38 PM
Author

In response to Clever_Anjos

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

753 Views
0 Likes
Clever_Anjos
Employee
Employee
‎2015-10-13 12:45 PM

In response to

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

753 Views
0 Likes
Clever_Anjos
Employee
Employee
‎2015-10-13 12:48 PM

In response to 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

753 Views
0 Likes
Clever_Anjos
Employee
Employee
‎2015-10-13 12:54 PM

In response to Clever_Anjos

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;
754 Views