Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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] ,
I am attempting to add another condition to this statement. If the [Inv_Grp] ,'FHA' then do one of the following;
if(day([K04 Date])<=20,date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],3))))),
Is there a way to integrate this into the code??
HI,
Try this
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],
if(day([K04 Date])<=20,
date(monthstart(addmonths([K04 Date],1))),date(monthstart(addmonths([K04 Date],3)))))
)))
as [trial1_due_dt] ,
Regards,
Jagan.
How would you like to do this? The only 'open' slot is the else clause for the last if statement - but at that point [KO4 Date] must be null or empty. AddMonths(null, ..) will only return null.
I have some problems with your current statement - laying it out:
If(len(trim([K04 Date])) <> 0,
//-- KO4 Date is not empty or null --
(
if(day([K04 Date]) <= 15,
date(monthstart(addmonths([K04 Date],1))),
date(monthstart(addmonths([K04 Date],2)))
)
),
//-- KO4 Date is empty/null --
if([LM Workout Status Act] = 'IN TRIAL',
[K04 Date],
If(len(trim([367])) <> 0,
//-- [367] is not empty or null --
(
if(day([367])<=15,
date(monthstart(addmonths([367],1))),
date(monthstart(addmonths([367],2)))
)
),
//-- [367] is empty/null (AND [KO4 Date] is also null
if([LM Workout Status Act]='IN TRIAL',
[367]
)
)
)
) as [trial1_due_dt],
If [KO4 Date] is null and [LM Workout Status Act] = 'IN TRIAL', then you return [KO4 Date] (which is null). See bold text in your expression. Is that your intention?
The same applies to [367]. If it is null and [LM Workout Status Act] = 'IN TRIAL', then you return [367] (null).
If I were solving this problem, I would draw out a flowchart describing all the possible outcomes and then build the If expression. I would also lay out the expression like the above to see exactly what is happening and confirm that the statement is correct.