topic Re: YTD Calculation as a Field in QlikView

YTD Calculation as a Field

Thu, 13 Aug 2015 17:02:23 GMT

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!

Re: YTD Calculation as a Field

swuehl — Thu, 13 Aug 2015 17:06:30 GMT

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

Re: YTD Calculation as a Field

maxgro — Thu, 13 Aug 2015 17:07:26 GMT

for YTD field

Year-over-Year Comparisons

Re: YTD Calculation as a Field

Thu, 13 Aug 2015 18:00:41 GMT

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?