Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

aboumejjane
Not applicable

problem with end date using a LET variable

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

7 Replies
celambarasan
Not applicable

problem with end date using a LET variable

Hi,

     Did you checked without $()?like this

Load

year,

month,

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

From Database......

Celambarasan

Miguel_Angel_Baeyens
Not applicable

Re: problem with end date using a LET variable

Hi iSam,

Just do not warp the expansion using single quotes:

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

Hope that helps.

Miguel

aboumejjane
Not applicable

Re: problem with end date using a LET variable

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

aboumejjane
Not applicable

problem with end date using a LET variable

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

Not applicable

problem with end date using a LET variable

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

Miguel_Angel_Baeyens
Not applicable

Re: problem with end date using a LET variable

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

aboumejjane
Not applicable

problem with end date using a LET variable

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