Datevar Array not working

[cloudstrifer]

Hi,

My following formula does not work and I don't have a clue why. I try to create a datevar array with values coming from sql expressions. Please help

DateVar Array holidays := [date({%CANADA DAY}), date({%CHRISTMAS DAY}), date({%CIVIC HOLIDAY}), date({%GOOD FRIDAY}), date({%LABOUR DAY}), date({%NEW YEARS DAY}), date({%THANKSGIVING}), date({%victoria day}) ];

//check for weekend and if next business day is a holiday
if dayofweek({@delivery matrix}) = 1 and {@delivery matrix} + 1 in holidays then {@delivery matrix} + 2
else if dayofweek({@delivery matrix}) = 7 and {@delivery matrix} + 2 in holidays then {@delivery matrix} + 3

// check for holidys only
else if {@delivery matrix} in holidays then {@delivery matrix} + 1

// if sunday then add 1 day
else if dayofweek({@delivery matrix}) = 1 then {@delivery matrix} + 1

// if saturday then add 2 days
else if dayofweek({@delivery matrix}) = 7 then {@delivery matrix} + 2

else {@delivery matrix}

 

[Charliy]

Could you define "not working"? What result did you expect and what result do you get?

 

[cloudstrifer]

Hi Clarliy,

When I use the formula, it doesn't add an extra day or two if the holiday lands on a weekday or weekend. Basically, the whole field is blank when I use the formula above.

however, when I use the formula below it works fine but I will need to modify it each year

//check for weekend and if next business day is a holiday
if dayofweek({@delivery matrix}) = 1 and {@delivery matrix} + 1 = date (2015, 1, 1) then {@delivery matrix} + 2
else if dayofweek({@delivery matrix}) = 7 and {@delivery matrix} + 2 = date (2015, 1, 1) then {@delivery matrix} + 3

else if dayofweek({@delivery matrix}) = 1 and {@delivery matrix} + 1 = date (2015, 2, 16) then {@delivery matrix} + 2
else if dayofweek({@delivery matrix}) = 7 and {@delivery matrix} + 2 = date (2015, 2, 16) then {@delivery matrix} + 3

else if dayofweek({@delivery matrix}) = 1 and {@delivery matrix} + 1 = date (2015, 4, 3) then {@delivery matrix} + 2
else if dayofweek({@delivery matrix}) = 7 and {@delivery matrix} + 2 = date (2015, 4, 3) then {@delivery matrix} + 3

else if dayofweek({@delivery matrix}) = 1 and {@delivery matrix} + 1 = date (2015, 5, 18) then {@delivery matrix} + 2
else if dayofweek({@delivery matrix}) = 7 and {@delivery matrix} + 2 = date (2015, 5, 18) then {@delivery matrix} + 3

else if dayofweek({@delivery matrix}) = 1 and {@delivery matrix} + 1 = date (2015, 7, 1) then {@delivery matrix} + 2
else if dayofweek({@delivery matrix}) = 7 and {@delivery matrix} + 2 = date (2015, 7, 1) then {@delivery matrix} + 3

else if dayofweek({@delivery matrix}) = 1 and {@delivery matrix} + 1 = date (2015, 8, 3) then {@delivery matrix} + 2
else if dayofweek({@delivery matrix}) = 7 and {@delivery matrix} + 2 = date (2015, 8, 3) then {@delivery matrix} + 3

else if dayofweek({@delivery matrix}) = 1 and {@delivery matrix} + 1 = date (2015, 9, 7) then {@delivery matrix} + 2
else if dayofweek({@delivery matrix}) = 7 and {@delivery matrix} + 2 = date (2015, 9, 7) then {@delivery matrix} + 3

else if dayofweek({@delivery matrix}) = 1 and {@delivery matrix} + 1 = date (2015, 10, 12) then {@delivery matrix} + 2
else if dayofweek({@delivery matrix}) = 7 and {@delivery matrix} + 2 = date (2015, 10, 12) then {@delivery matrix} + 3

else if dayofweek({@delivery matrix}) = 1 and {@delivery matrix} + 1 = date (2015, 12, 25) then {@delivery matrix} + 2
else if dayofweek({@delivery matrix}) = 7 and {@delivery matrix} + 2 = date (2015, 12, 25) then {@delivery matrix} + 3


// check for holidys only
else if {@delivery matrix} = date (2015, 1, 1) then {@delivery matrix} + 1

else if {@delivery matrix} = date (2015, 2, 16) then {@delivery matrix} + 1

else if {@delivery matrix} = date (2015, 4, 3) then {@delivery matrix} + 1

else if {@delivery matrix} = date (2015, 5, 18) then {@delivery matrix} + 1

else if {@delivery matrix} = date (2015, 7, 1) then {@delivery matrix} + 1

else if {@delivery matrix} = date (2015, 8, 3) then {@delivery matrix} + 1

else if {@delivery matrix} = date (2015, 9, 7) then {@delivery matrix} + 1

else if {@delivery matrix} = date (2015, 10, 12) then {@delivery matrix} + 1

else if {@delivery matrix} = date (2015, 12, 25) then {@delivery matrix} + 1

// if sunday then add 1 day

else if dayofweek({@delivery matrix}) = 1 then {@delivery matrix} + 1

// if saturday then add 2 days

else if dayofweek({@delivery matrix}) = 7 then {@delivery matrix} + 2


else {@delivery matrix}

 

[Charliy]

Try setting up the array with actual dates. If that works then the issue is with your SQL expression.