topic Re: problem with end date using a LET variable in QlikView

problem with end date using a LET variable

Anonymous — Tue, 10 Apr 2012 15:28:13 GMT

Hi Guys,

I have the following variable:

LET vPeriod = date(Makedate(YEAR,Month,1),'YYYYMM');

When I use one of these expression in my field date(Makedate(YEAR,Month,1),'YYYYMM') as Period, it works

Now I want to create the end period using the Let statement 'vPeriod'

Load

year,

month,

Date(monthstart(addmonths('$(vPeriod)',12)),'YYYYMM') as EndPeriode

From Database......

I don't get any error when reloading. However no data can be found in the EndPeriode.

What am I doing wrong?

iSam

problem with end date using a LET variable

CELAMBARASAN — Tue, 10 Apr 2012 15:41:00 GMT

Hi,

Did you checked without $()?like this

Load

year,

month,

Date(monthstart(addmonths(vPeriod,12)),'YYYYMM') as EndPeriode

From Database......

Celambarasan

Re: problem with end date using a LET variable

Miguel_Angel_Baeyens — Tue, 10 Apr 2012 15:44:13 GMT

Hi iSam,

Just do not warp the expansion using single quotes:

Date(monthstart(addmonths($(vPeriod),12)),'YYYYMM') as EndPeriode

Hope that helps.

Miguel

Re: problem with end date using a LET variable

Anonymous — Tue, 10 Apr 2012 17:20:37 GMT

Hi Miguel,

If i do that, I get the following error:

Error in expression:

')' expected

My expression seems to be oke right?

date(monthstart(addmonths($(vPeriod),12)),'YYYYMM') as EndPeriod

Thanks for you help!

Isam

problem with end date using a LET variable

Anonymous — Tue, 10 Apr 2012 17:22:47 GMT

Hi,

If I remove the $ sign I get the error that the field cannot be found. Which makes sense

Thanks for your help so far.

Isam

problem with end date using a LET variable

Wed, 11 Apr 2012 09:27:10 GMT

Hi Aboumejjane,

Maybe this solution is not so smart, but I think it works:

LET vEndPeriod = Date(monthstart(addmonths(vPeriod,12)),'YYYYMM');

LOAD data,

Year,

Month,

if (1=1,$(vEndPeriod)) as EndPeriode

FROM ...

I hope it helps

Regards

Giampiero

Re: problem with end date using a LET variable

Miguel_Angel_Baeyens — Wed, 11 Apr 2012 09:42:30 GMT

Hi Isam,

You are using Date() function with a value that is not a date (the variable). According to your LET script above, the following works here, note the Date#() function:

Date(MonthStart(AddMonths(Date#($(vPeriod), 'YYYYMM'), 12)), 'YYYYMM')

Hope that helps.

Miguel

problem with end date using a LET variable

Anonymous — Thu, 19 Apr 2012 13:55:02 GMT

Hi Miguel,

Thanks for your help and sorry for my very late response!

I found the problem. I was using the date function twice, once in the in the variable and once in my expression.

It should have been Let vPeriod = Makedate(Year,Month,1);

Load

Date(monthstart(addmonths('$(vPeriod)',12)),'YYYYMM') as EndPeriod

I haven't tried your solution yet, But I assume that that will work also.

Many thanks for your help!!!

iSam