Financial Year Calendar

ivandrago — Wed, 25 Mar 2015 09:56:45 GMT

Hi,

I have the below code I use for my Calendar, is there away to change it so it relates to financial year instead of calendar year? Is it best to just add extra fields to the Calendar so I can see the Financial flags as well? Our Financial Year starts between 1st April to 31st March.

[StartDate]:

LOAD min([Date]) as Oldest

FROM

(qvd);

LET vStartDate = If(peek('Oldest')<=monthstart(addmonths($(vToday),-13)),

peek('Oldest')-1,

monthstart(addmonths($(vToday),-13))-1); // the -1 makes loop easier

LET vEndDate = num(yearend(today()));

SET vCompStartDate = monthstart(addmonths(today(),-14));

Calendar:

LOAD Date,

year(Date) as Year,

month(Date) as Month,

monthstart(Date) as [Month Start],

'W'&week(Date) as Week,

month(Date)&'-'&Year(Date) as [Month Year],

'Q'&Ceil(Month(Date)/3) as Quarter,

QuarterName(Date) as [Quarter Name],

day(Date) as Day,

weekday(Date) as Weekday,

weekday(Date)&' '&day(Date) as [Day of Week],

if(Date=$(vToday), 1) as [CD Flag],

// if(Date=($(vToday)-1),1)as [PD Flag],

if(Date=($(vToday)-1),1)as [PD Flag 2],

if(year(Date)=year($(vToday)),1) as [CY Flag],

if(year(Date)=year(addyears($(vToday),-1)),1)as [PY Flag],

If((Date>=YearStart($(vToday)) and Date<=$(vToday)), 1) as [CYTD Flag],

If((Date>=YearStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYTD Flag],

if(QuarterName(Date)=QuarterName($(vToday)),1) as [CQ Flag],

If((Date>=QuarterStart($(vToday)) and Date<=$(vToday)), 1) as [CQTD Flag],

If((Date>=QuarterStart(AddMonths($(vToday),-3)) and Date<=AddMonths($(vToday),-3)), 1) as [PQTD Flag],

If((Date>=QuarterStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYQTD Flag],

If((Date>=MonthStart($(vToday)) and Date<=MonthEnd($(vToday))), 1) as [CM Flag],

If((Date>=MonthStart($(vToday)) and Date<=$(vToday)), 1) as [CMTD Flag],

If((Date>$(vToday) and Date<=MonthEnd($(vToday))), 1) as [RCM Flag],

If((Date>=MonthStart(AddMonths($(vToday),-1)) and Date<=AddMonths($(vToday),-1)), 1) as [PMTD Flag],

If((Date>=MonthStart(AddMonths($(vToday),-1)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [PM Flag],

If((Date>=MonthStart($(vOneYearAgo)) and Date<=$(vOneYearAgo)), 1) as [PYMTD Flag],

If((Date>=MonthStart(AddMonths($(vToday),-3)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [R3 Flag],

If((Date>=MonthStart(AddMonths($(vToday),-12)) and Date<=MonthEnd(AddMonths($(vToday),-1))), 1) as [R12 Flag],

If((Date>=MonthStart(AddMonths($(vOneYearAgo),-3)) and Date<=MonthEnd(AddMonths($(vOneYearAgo),-1))), 1) as [PY R3 Flag],

If((Date>=MonthStart(AddMonths($(vOneYearAgo),-12)) and Date<=MonthEnd(AddMonths($(vOneYearAgo),-1))), 1) as [PY R12 Flag],

If((Date>=WeekStart($(vToday)) and Date<=WeekEnd($(vToday))), 1) as [CW Flag],

If((Date>=WeekStart($(vToday)-7) and Date<=WeekEnd($(vToday)-7)), 1) as [PW Flag],

If((Date>=WeekStart($(vToday)) and Date<=$(vToday)), 1) as [CWTD Flag],

If((Date>$(vToday) and Date<=WeekEnd($(vToday))), 1) as [RCW Flag],

If(Year(Date)>=year(addmonths(today(),-13)),Year(Date)) as [New Year];

LOAD date($(vStartDate) + IterNo()) as Date

AUTOGENERATE 1

WHILE $(vStartDate) + IterNo() <= $(vEndDate);

[Analysis Periods]:

LOAD monthstart(addmonths($(vCompStartDate), 1*IterNo())) as [Month Start],

IterNo() as [Analysis Period]

AUTOGENERATE 1

WHILE IterNo() <= 14;

Re: Financial Year Calendar

PrashantSangle — Wed, 25 Mar 2015 09:58:57 GMT

Hi,

Use YearName()

YearName(date,0,4)

Regards

Re: Financial Year Calendar

ivandrago — Wed, 25 Mar 2015 10:11:39 GMT

Hi,

Thanks for your help so far, but I need the current fields in the calendar to relate to the Financial Year some how, as the above function does not help with my existings charts I have as I use a Cyclic which is flicks between Days/Month/Year.

Thanks

topic Financial Year Calendar in QlikView

Financial Year Calendar

Re: Financial Year Calendar

Re: Financial Year Calendar