topic Re: Read only the current 3 years of data using a variable (No hard-coding) in QlikView

Read only the current 3 years of data using a variable (No hard-coding)

Nagaraju_KCS — Thu, 28 Feb 2013 08:11:17 GMT

Hi all,

Read only the current 3 years of data using a variable (No hard-coding)

Please explain... it;s very urgent....

Re: Read only the current 3 years of data using a variable (No hard-coding)

swuehl — Thu, 28 Feb 2013 09:36:27 GMT

Not sure if I understood your request ('current 3 years' means last 3 years incl. current year?).

To do this, you don't necessarily need a variable, but you can use one:

Let vStartYear = year(today())-2;

LOAD

Year,

Data,

...

FROM ... where Year >= $(vStartYear);

Re: Read only the current 3 years of data using a variable (No hard-coding)

Nagaraju_KCS — Thu, 28 Feb 2013 17:00:08 GMT

Hi swuehl,

But how to use in pivot table...

please give me sample expression

Please Explain

Re: Read only the current 3 years of data using a variable (No hard-coding)

swuehl — Thu, 28 Feb 2013 17:19:19 GMT

Not sure what you want to achieve or how your data looks like, it would really help if you could explain your request in a bit more detail.

Maybe using either a calculated dimension

=(if Year >= vStartYear, Year)

or in your expressions, use aggregation functions with a set expression:

= sum( {<Year ={">=$(vStartYear)"} >} Value)

Re: Read only the current 3 years of data using a variable (No hard-coding)

Nagaraju_KCS — Thu, 28 Feb 2013 17:38:28 GMT

Hi,

Please see the below link..

http://community.qlik.com/message/316853#316853

Re: Read only the current 3 years of data using a variable (No hard-coding)

Thu, 28 Feb 2013 18:09:05 GMT

If you are trying to load only three years of data from the edit script feature, use:

Let vStartYear = year(today())-2;

LOAD

Year,

Data,

...

FROM ... where Year >= $(vStartYear);

OR if you are trying to load three years of data from today, you can use months:

Let vTodayMinusXMonths= date(addmonths(today(),-36),'YYYYMMDD')

LOAD

Date,

Year,

Data;

SQL SELECT *

FROM ...

where Date>=$(vTodayMinusXMonths);

OR if you are trying to only show/load three years of data from a field in a table from today, use set analysis in the expression (for example, if you are trying to count sales for specific years)

count({$<Year={$(=max(Year), =max(Year)-1), =max(Year)-2) }>} [Sales])

if you know the years, it's easier:

count({$<Year= {2013, 2012, 2011}>} [Sales])