how to do a rolling 13 in SQl select statement?

[#1]

Hi,

Here i am pulling the data from source and i want to get the data only from past 13 months rolling.

i was trying to use a variable to store this,

Let vRolling=date(MonthStart(AddMonths(Date(Today()),-13)))

then,

SQL SELECT *

FROM "Table_name" Where Date_Field>$(vRolling)

i am getting an error which says script line error, can some one help me whats wrong with the path?

I know that we cannot use qlikview functions during ODBC query but i tried.

[jagan · Accepted solution]

Hi,

Let vRolling = date(MonthStart(AddMonths(Date(Today()), -13)), 'yyyy-m-d');

TEst:

SELECT

   *

FROM TableName

WHERE Date_Field> '$(vRolling)';

This works for me, in the above let statement change date format with your date format.

Regards,

Jagan.

[Anonymous]

How is the format of the field Date_Field?

Date_Field = ddmmyyyy

or

Date_Field = dd-mm-yyyy;

then the variable vRolling be equal to the format Date_Field

you try these,

then,

SQL SELECT *

FROM "Table_name" Where Date_Field>'$(vRolling)';

[#4]

I did change the format to which the source has which 'MM/DD/YYYY' no still i see the script line error.

Let vRolling=date(MonthStart(AddMonths(Date(Today()),-13)),'MM/DD/YYYY')

FYI- before this i gave a hard code value '03/04/2012' it was working fine Also this an ODBC query. Not sure if something needs to be tweaked arounf the format or the script?

[#5]

Hi Alejandro,

Date_field format on the source is 'MM/DD/YYYY', i have tried to mtach the format to the let statement variable but no luck?

[Anonymous]

Let vRolling=date(MonthStart(AddMonths(Date(Today()),-13)),'MM/DD/YYYY');

Prueba:

SQL SELECT *

FROM "Table_name" Where Date_Field>'$(vRolling)';

[jagan]

Hi,

Can you attach the sample file with script and sample data?

Regards,

Jagan.

[#8]

Yes, thats exactly what i have tried to do, but no luck.

[Anonymous]

you are missing semicolon

Let vRolling=date(MonthStart(AddMonths(date(today()),-13)),'MM/DD/YYYY');

$(_qvd_name):

SQL SELECT *

FROM "DIM_TR"."T_TRAN" where NC_TRANS_DT>='$(vRolling)';

[#10]

Ohh god i am so sorry all through this time that was the only mistake i was making it was the semicolon. it is perfectly working thanks Alejandro.