topic Re: Problems With AddMonths in QlikView

Problems With AddMonths

rebelfox — Mon, 05 Oct 2015 14:48:21 GMT

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?

Re: Problems With AddMonths

Peter_Cammaert — Mon, 05 Oct 2015 14:53:00 GMT

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.

Re: Problems With AddMonths

jpapador — Mon, 05 Oct 2015 14:59:09 GMT

Have you tried it without the variable?

AddMonths(Today(),-6)

Re: Problems With AddMonths

malini_qlikview — Mon, 05 Oct 2015 14:59:30 GMT

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

Let VarTest = AddMonths(VarTest,-6);

Re: Problems With AddMonths

hic — Mon, 05 Oct 2015 14:59:44 GMT

Just remove the dollar expansion:

Let VarTest = Today();

Let VarTest = AddMonths(VarTest,-6);

HIC

Re: Problems With AddMonths

rebelfox — Mon, 05 Oct 2015 15:00:19 GMT

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?

Re: Problems With AddMonths

rebelfox — Mon, 05 Oct 2015 15:11:55 GMT

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

Re: Problems With AddMonths

rebelfox — Mon, 05 Oct 2015 15:12:42 GMT

Yes this works as well. Thanks.

Re: Problems With AddMonths

Peter_Cammaert — Mon, 05 Oct 2015 15:14:54 GMT

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