topic Re: Filtering data based on date in QlikView

Filtering data based on date

sridhar_sigired — Mon, 10 Aug 2015 09:08:26 GMT

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

Re: Filtering data based on date

sunny_talwar — Mon, 10 Aug 2015 09:10:55 GMT

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

Re: Filtering data based on date

Mon, 10 Aug 2015 09:12:12 GMT

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

Re: Filtering data based on date

sridhar_sigired — Mon, 10 Aug 2015 09:13:45 GMT

It is showing the error as

Field not found - <and>

Can you please why so?

Re: Filtering data based on date

sridhar_sigired — Mon, 10 Aug 2015 09:14:45 GMT

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

Showing below error.

Field not found - <and>

Re: Filtering data based on date

sunny_talwar — Mon, 10 Aug 2015 09:17:12 GMT

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);

Re: Filtering data based on date

sridhar_sigired — Mon, 10 Aug 2015 09:20:31 GMT

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.

Re: Filtering data based on date

sunny_talwar — Mon, 10 Aug 2015 09:23:56 GMT

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

Re: Filtering data based on date

sridhar_sigired — Mon, 10 Aug 2015 09:26:47 GMT

Thanks Sunny.

Re: Filtering data based on date

sunny_talwar — Mon, 10 Aug 2015 09:28:44 GMT

No problem Sridhar

I am glad I was able to help.

Best,

Sunny

Re: Filtering data based on date

Mon, 10 Aug 2015 10:42:50 GMT

Can you please load your app here