Discussion Board for collaboration on QlikView Scripting.
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 ?
Go to Solution.
Try to play around with something like this in your script:
LET YearBoundary = Year(Today()) - 3;
you can use this expression in your varialbe
for the YearBoundary3 variable you can use
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')
Now is working fine.
I was wrong to insert the script :-
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.