using Variable.

[qv_testing]

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..

[tresesco]

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

[tresesco]

Please share your sample app.

[christian77]

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.

[tresesco · Accepted solution]

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;

[qv_testing]

Thank you very much tresesco