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: 
Anonymous
Not applicable

Resolving script line errors

I am trying to evaluate if a date value is greater then or less then a specific date and set that variable. I have tried two ways (both below) with no dice. Any ideas?

//Date Calculations

LET vMonthStart =  DayEnd(MonthStart(today(),-$(vMonthBack)),-1);

LET vMonthEnd = DayStart(MonthEnd(today(),-$(vMonthBack)),1);

LET vMonth = MonthName(today(),-$(vMonthBack));

//Advanced Variables

//Second Try, does not report the specific row error

LET vFrom = IF($(vMonthStart)<'11/01/2015',$(vFromOld),IF($(vMonthStart)>'10/30/2015',$(vFromNew)));

//First try is below (both rows) - says error at ","

// IF($(vMonthStart)<"11/01/2015", LET vFrom = $(vFromOld));

// IF($(vMonthStart)>"10/30/2015", LET vFrom = $(vFromNew));

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

The variables are $ expanded before being evaluated and you will not get the results you expect. Remove the $ expansions here:

LET vFrom = IF(vMonthStart < '11/01/2015', vFromOld

     ,IF(vMonthStart > '10/30/2015', vFromNew));

But have vFromOld and vFromNew been defined yet? If they are null, then the expression will fail. This also assumes that MM/dd/yyyy is the default date format.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
tamilarasu
Champion
Champion

Hi Chris,

Try,

LET vMonthStart =  Date(Floor(DayEnd(MonthStart(today(),-$(vMonthBack)),-1)),'MM/DD/YYYY');

LET vMonthEnd = Date(Floor(DayStart(MonthEnd(today(),-$(vMonthBack)),1)),'MM/DD/YYYY');

LET vMonth = MonthName(today(),-$(vMonthBack));

LET vFrom = IF($(vMonthStart)<'11/01/2015',$(vFromOld),IF($(vMonthStart)>'10/30/2015',$(vFromNew)));

jagan
Luminary Alumni
Luminary Alumni

Hi Chris,

Try like this

//Date Calculations 

LET vMonthStart =  DayEnd(MonthStart(today(),-$(vMonthBack)),-1); 

LET vMonthEnd = DayStart(MonthEnd(today(),-$(vMonthBack)),1); 

LET vMonth = MonthName(today(),-$(vMonthBack));   

LET vFrom = vMonthStart < MakeDate(2015, 11, 1), vFromOld, IF(vMonthStart > MakeDate(2015, 10, 30), vFromNew)); 

Regards,

Jagan.

vlad_komarov
Partner - Specialist III
Partner - Specialist III

You need to be careful when creating variables with special characters or quotes.

I would suggest to use chr() function to compose the string.

Examples:

let vBack = '=' & chr(39) & '(' & chr(39) & chr(38) & 'Concat({$1}[FIELD1], ' & chr(39) & chr(124) & chr(39) & ')' & chr(38) & chr(39) & ')' & chr(39);

let vIndex = '=index(' & chr(36) & chr(40) & 'vYNFull' & chr(41) & ',' & chr(39) & chr(36) & chr(40) & 'vCurrSelectedYearMonth' & chr(41) & chr(39) & ')';

jonathandienst
Partner - Champion III
Partner - Champion III

The variables are $ expanded before being evaluated and you will not get the results you expect. Remove the $ expansions here:

LET vFrom = IF(vMonthStart < '11/01/2015', vFromOld

     ,IF(vMonthStart > '10/30/2015', vFromNew));

But have vFromOld and vFromNew been defined yet? If they are null, then the expression will fail. This also assumes that MM/dd/yyyy is the default date format.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Sorry, a point of clarification: the issue is with the advanced variable creation (not the data variables - these can't change as they are used elsewhere). Line 3, below, is my latest try - lines 5 and 6, below, are my prior tries.

  1. //Advanced Variables 
  2. //Second Try, does not report the specific row error 
  3. LET vFrom = IF($(vMonthStart)<'11/01/2015',$(vFromOld),IF($(vMonthStart)>'10/30/2015',$(vFromNew))); 
  4. //First try is below (both rows) - says error at "," 
  5. // IF($(vMonthStart)<"11/01/2015", LET vFrom = $(vFromOld)); 
  6. // IF($(vMonthStart)>"10/30/2015", LET vFrom = $(vFromNew));