Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

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
MVP
MVP

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

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

5 Replies
jakob_rasmussen
New Contributor III

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

Hi,

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

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

/Jakob

lironbaram
Honored Contributor II

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

hi

you can use this expression in your varialbe

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

MVP
MVP

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

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

MVP
MVP

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

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
Valued Contributor II

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

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

Community Browser