Problems With AddMonths

[rebelfox]

I am having problems all over the place with dates.

My first problem is that I need a variable to calculate todays date minus six months ago for a LOAD statement WHERE clause.

I've tried the code below just to set the variable but is all goes horribly wrong.

In debug I get '05-10-2015' after the first statement but '19-12-1893' after the second statement.

Let VarTest = Today();

Let VarTest = AddMonths($(VarTest),-6);

Where am I going wrong?

[Peter_Cammaert · Accepted solution]

The first version of VarTest probably contains something like 5/10/2015. Passing this division as-is as a parameter to AddMonths() will not result in a correct value. Subtractions in your case, not divisions.

Put $(VarTest) between single quotes or add num() to the first line, reload and see what happens.

Peter

[Edit]: Wrong feedback. But you get the idea. Use a quoted variable in the second line, or add num() to the first.

[jpapador]

Have you tried it without the variable?

AddMonths(Today(),-6)

[malini_qlikview]

just remove the $ symbol in the variable, it will work!

Let VarTest = AddMonths(VarTest,-6);

[hic]

Just remove the dollar expansion:

Let VarTest = Today();

Let VarTest = AddMonths(VarTest,-6);

HIC

[rebelfox]

OK both of your examples work.

I'm a bit confused over why.  Taking your first solution, why is it necessary for the variable to be converted to a number on the first line and why does the subsequent AddMonths change the format from a number back to a date?

[rebelfox]

My next newbie query relates to the LOAD statement.

I compare a timestamp value on the table to the variable in the hope it would select dates in the last 6 months but the date selection seems to be ignored.

WHERE SA_Changed_Date >= ($VarTest2) AND

[rebelfox]

Yes this works as well. Thanks.

[Peter_Cammaert]

The problem is with the $-sign expansion. DSE replaces $(var) with the content of var. This results in a second statement like:

Let VarTest = AddMonths(05-10-2015,-6);

which is an arithmetic expression.

AddMonths on the other hand will accept both strings and numerical (and dual()) values as a first parameter. And return a dual() date value which - depending on the context - can result in the string representation or the numerical value that corresponds to that particular date.

See also Henric's post down below. He suggests an even better (simpler) alternative.

Good luck,

Peter