Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Read only the current 3 years of data using a variable (No hard-coding)
Please explain... it;s very urgent....
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);
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)
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])