Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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));
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.
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)));
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.
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) & ')';
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.
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.