Year Boundary 1/1/2012 is 3 yr ago during 1/1/2015 How to set it become dynamic ?

[paulyeo11]

Hi All

In my script below :-

if(date(@168:179,'DD/MM/YYYY') > '$(YearBoundary3)',dual('No', 0), dual('Yes', 1)) as  [No Sales > 3yr],

In my variable setting , i set YearBoundary3 = 1/1/2012 ( See below image )

Since tomorrow 1/1/2016 , and again 1/1/2012 , the duration is 4 year . which is wrong.

I need to manually change the variable setting to YearBoundary3 = 1/1/2013 , this way is very manual and need maintenance.

Can some one share with me how to make it more dynamic ?

[jakob_rasmussen]

Hi,

Try to play around with something like this in your script:

LET YearBoundary = Year(Today()) - 3;

/Jakob

[lironbaram]

hi

you can use this expression in your varialbe

=addmonths(yearstart(today()),-36)

[tresesco · Accepted solution]

Or,       AddYears(YearStart(today()),-3)

[maxgro]

for the YearBoundary3 variable you can use

=YearStart(AddYears(Today(),-3))

for the if, as your field is 12 char (168:179), my guess is you can try with date# to interpret the field and then date to format; something like

date(date#(yourfield, 'yourfieldformat'), 'DD/MM/YYYY')

[paulyeo11]

Hi All

Now is working fine.

I was wrong to insert the script :-

=addmonths(yearstart(today()),-36)

at the variable setting.

instead i should do it in load script :-

LET YearBoundary3 = MakeDate(year(Today())-2);

LET YearBoundary2 = MakeDate(year(Today())-1);

LET YearBoundary = MakeDate(year(Today())-1);

Thank you very one help.

Paul