Filtering data based on date

[sridhar_sigired]

Dear Team,

I have 2007-2011 data, where i have to read only latest 3 years data. I have order date field. I am trying with below line, but not working. Can you please give me the solution.

Created two variables - vMaxYear    =Year(today())-4 (2011)

                                  vMinYear     =Year(today())-6 (2009)

Load

    OrderID,

     Year(OrderDate) as YearOrderDate,

     ShipperID

FROM

[..\Stage1\Orders.qvd]

(qvd) Where Year(OrderDate) >=$('vMinYear') and Year(OrderDate) <=$('vMaxYear');

Regards,

Sridhar

[sunny_talwar]

Try this:

Load

    OrderID,

     Year(OrderDate) as YearOrderDate,

     ShipperID

FROM

[..\Stage1\Orders.qvd]

(qvd) Where Year(OrderDate) >=$(vMinYear) and Year(OrderDate) <=$(vMaxYear); //Use variables without single quotes

[#3]

Hi Sridhar,

Please try removing the quotes for the variables. Also create a text box in the front end and please evaluate your variable if they are giving correct result or not

Regards

chitra

[sridhar_sigired]

It is showing the error as

Field not found - <and>

Can you please why so?

[sridhar_sigired]

I have checked the variables from front end. Still without quotes it is not working Chitra.

Showing below error.

Field not found - <and>

[sunny_talwar · Accepted solution]

Do you have this before your load?

LET vMaxYear =Year(Today())-4;

LET vMinYear =Year(Today())-6;

Load

    OrderID,

     Year(OrderDate) as YearOrderDate,

     ShipperID

FROM

[..\Stage1\Orders.qvd]

(qvd)

Where Year(OrderDate) >=$(vMinYear) and Year(OrderDate) <=$(vMaxYear);

[sridhar_sigired]

Hi,

I have created variables from variable overview, now i have tried doing with your solution and it is working. Is it must? does not it work if we create from variable overview?

Thanks.

[sunny_talwar]

I would think that once it is created in variable overview, it should work in the script, but I may be wrong as I have never tested it out. To be on a safer side, I have always created the variable in the script whenever I wanted to use it in the script.

HTH

Best,

Sunny

[sridhar_sigired]

Thanks Sunny.

[sunny_talwar]

No problem Sridhar

I am glad I was able to help.

Best,

Sunny