Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

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 ?

year boundary.png

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

View solution in original post

5 Replies
jakob_rasmussen
Contributor III
Contributor III

Hi,

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

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

/Jakob

lironbaram
Partner - Master III
Partner - Master III

hi

you can use this expression in your varialbe

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

tresesco
MVP
MVP

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

maxgro
MVP
MVP

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
Master
Master
Author

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