topic using Variable. in QlikView

using Variable.

Nagaraju_KCS — Tue, 10 Sep 2013 07:22:35 GMT

Hi All,

i have 2002, 2003 to 2007 data in my database

i want return data last 3 years of data in my database using variable (not Hard coding)

Thanks..

Re: using Variable.

tresB — Tue, 10 Sep 2013 07:27:50 GMT

You have to load the data to get the max year and then calculate, something like:

Max:

Load Max(Year) as MaxYear From <DB>;

Let vMaxYear=Peek('MaxYear');

Let vFromYear=vMaxYear-3;

Drop table Max;

FinalTable:

Load * From <DB> where YearField>='$(vFromYear)' ;

Re: using Variable.

tresB — Tue, 10 Sep 2013 08:04:09 GMT

Please share your sample app.

Re: using Variable.

christian77 — Tue, 10 Sep 2013 08:10:27 GMT

Hi:

For loading:

vPriorYear2=year(today())-2 --> 2011

LOAD *;

WHERE Year >= $(vPriorYear)

For calcullations:

vLast3Years='"' & concat({1<Year={'>=$(vPriorYear2)'}>} distinct Year,'","') & '"' --> 2011,2012,2013

sum({$<Year={‘$( vLast3Years)’}>} Amount)

Luck.

Re: using Variable.

tresB — Tue, 10 Sep 2013 08:29:12 GMT

Try this code:

Max:
Load Max(OrderDate) as MaxDate;

SQL SELECT OrderDate
FROM Orders;

Let vMaxYear=Year(Peek('MaxDate'));
Let vFromYear=vMaxYear-3;
Drop Table Max;

LOAD CustomerID,
    EmployeeID,
    Freight,
    OrderDate,
    OrderID,
    ShipperID
Where Year(OrderDate)>='$(vFromYear)';

SQL SELECT *
FROM Orders;

Re: using Variable.

Nagaraju_KCS — Tue, 10 Sep 2013 08:40:13 GMT

Thank you very much tresesco