YTD Calculation as a Field

[#1]

Hi,

I'm trying to construct a bar chart with calculated returns for 1 month, 3month, 1 year etc. but most importantly YTD. In order to this do I need to define YTD as a field during by my OLE DB data pull-in?

At the moment I have:

LOAD Date,
Year(Date) as Year,
Month(Date) as Month,
Year(Date) & '_' & num(Month(Date)) as YearMonth,
If(IsNum(Month(Date)),'Q'& Ceil(Month(Date),3)/3) as Quarter,

Can I add to this to get a year to date field? Furthermore, how would I get 1M, 3M, 1YR fields for the chart?

Thanks in advance!

[swuehl · Accepted solution]

You can use date and time QV functions and flag the dates in your calendar, as shown here:

Relative Calendar Fields

Also check the HELP for

YearToDate() and InYearToDate() functions

[maxgro]

for YTD field

Year-over-Year Comparisons

[#4]

Okay, so I have now added a master calendar:

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 

Temp: 
Load 
min(CBDate) as minDate, 
max(CBDate) as maxDate 
Resident SecurityData; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
DROP Table Temp; 

TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 
Load 
TempDate AS CBDate, 
week(TempDate) As Week, 
Year(TempDate) As Year, 
Month(TempDate) As Month, 
Day(TempDate) As Day, 
YeartoDate(TempDate)*-1 as CurYTDFlag, 
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
12*(Year(Today())-Year(Date)) + Month(Today()) - Month(Date) as MonthsAgo,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

But am having trouble inserting this field  12*(Year(Today())-Year(Date)) + Month(Today()) - Month(Date) as MonthsAgo, in order to calculate 1month, 2month etc.

How would I do this?