Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
wonder if you can help me on this.
I am trying to load data but only for last 10 years. I cant figure out what am i doing wrong here. Can you please help out?
Last10Years:
Load
Year("Date") as [Year],
1 AS Flag_Last10years
FROM [$(vDataFolder)DAT_FileData\Data Folder\GBP Data.xlsx]
(ooxml, embedded labels, header is 3 lines, table is GBP)
Where Year("Date") >= Max(Year("Date")) -10
;
Can you please help out?
Thanks
May be something like this:
Last10Years:
LOAD Year("Date") as [Year],
1 as Flag_Last10years
FROM [$(vDataFolder)DAT_FileData\Data Folder\GBP Data.xlsx]
(ooxml, embedded labels, header is 3 lines, table is GBP);
Right Join (Last10Years)
LOAD Max(Year) - 11 + IterNo() as Year
Resident Last10Years
Where IterNo() <= 10;
Hi Sunny,
That doesnt work, only brings in one year.
I have attached the qvw file. See if you can fix it on there.
I have to load data for all years, then in my expressions i have to use last 20, 15, 10,5 years separately. i.e there will a chart showing last 15 years, another chart showing 10 years and so on. I want to load each of them separately so that in my set analysis i can use rowno() rather than hardcoding them to a year else end user will have to change it every year.
can you help on this?
Regards,
Hi
You can use the following filter condition,
Where Date>=YearStart(Today(),-10);
This will give your last 10 years data based on calendar date.
Please find the attached QVW for reference.
Reagrds
Harish
Moderators, can you please approve this so that we can have replies on it??
If the "last year" is always the current calendar year, you can use year(today()), and the second load is not needed:
WHERE year("Date") >= year(today()) -10
May be try this:
Right Join (AllYears)
LOAD MaxYear - 11 + IterNo() as Year,
Iterno() as LineID
While IterNo() <= 10;
LOAD Max(Year) as MaxYear
Resident AllYears;