topic Fiscal Calendar in QlikView

Fiscal Calendar

Mon, 11 Jan 2010 18:19:21 GMT

I am trying to convert my dates to be used on a fiscal calendar (starting July 1st) instead of a standard calendar (Start Jan. 1st):

I usually use this code to set up my master caledar: ( have added fiscal year, which works but i do not know how to change the quarter and the months to show the correct dates)

Any ideas?[8-|]















































































 

 












 

























 
//********************* Quarters ************************

QuartersMap:

MAPPING





LOAD
rowno

() as Month,
'Q'

& ceil((rowno()/3)) as Quarter
AUTOGENERATE(12);
LET




 vMaxDate = CurrentDate+10
;
Let




 vMinDate = ReportStartDate-0
;
LET




 v7days = today()-7
;
LET




 vNxt7days = today()+8
;
LET




 v14days = today()-14
;
LET




 v21days = today()-21
;
LET




 vNxt14days = today()+15
;
LET




 v30days = today()-30
;
LET




 vNxt30days = today()+30
;
LET




 v60days = today()-60
;
LET




 vToday = today()
;
LET




 vLastYear = makedate(year(today())-1,month(today()),day(today()))
;
LET




 v30daysLastYear = (makedate(year(today())-1,month(today()),day(today())))-30
;
LET




 vNxt30daysLastYear = (makedate(year(today())-1,month(today()),day(today())))+30
;
//********************* Temporary Calendar *********************

TempCalendar:

UnQualify





*;
LOAD

$(vMinDate)




Date(($(vMinDate)+IterNo()-1),'M/D/YYYY') AS TempDate
autogenerate



 
MasterCalendar:

LOAD

date

(TempDate,'M/D/YYYY') AS OrderDate,
date

(yearstart(TempDate,0,7),'YYYY') as FiscalYear,
date

(yearstart(TempDate,0,7),'MMM-YY') as FiscalMonth,
date

(TempDate) AS FullDate,
if

(right(weekname(TempDate, 0, -1),2) = 01 AND year(TempDate) < year (weekend(TempDate, 0, -1)) ,
53, right(weekname(TempDate, 0,-1),2)) AS Week'
if(weekday(yearstart(TempDate))='Mon' or 'Sun',if(month(TempDate)='Dec',if(week(TempDate)=1,53,week(TempDate)),week(TempDate)),if((week(TempDate)-1)=0,53,week(TempDate)-1)) AS WeekofYear,
weekstart

(TempDate, 0, -1) & ' - ' & weekend(TempDate, 0, -1) AS WkRange,
weekstart

(TempDate, 0, -1) AS WkStart,
weekend

(TempDate, 0, -1) AS WkEnd'
month

(TempDate) AS Month,,
date

(TempDate,'MMM-YY') AS MonthYear,
day

(TempDate) AS Day,
num

(month(TempDate)) & '/' & day(TempDate) AS MonthDay,
dayname

(TempDate) AS DayName,
weekday

(TempDate) AS Weekday,
year

(TempDate) AS Year,
applymap

('QuartersMap', num(month(TempDate)), null()) AS Quarter,
applymap

('QuartersMap', num(month(TempDate)), null()) & ' - ' & year(TempDate) AS QuarterYear
RESIDENT

TempCalendar;
DROP




 TABLE
 TempCalendar;

Any ideas

1 WHILE$(vMinDate)+IterNo()-1<=$(vMaxDate);+IterNo()-1 AS Num'

Fiscal Calendar

Mon, 11 Jan 2010 18:20:39 GMT

Wow that posted funny. Sorry about that.

Fiscal Calendar

Mon, 11 Jan 2010 18:24:02 GMT

Lets try....


//********************* Quarters ************************
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & ceil((rowno()/3)) as Quarter
AUTOGENERATE(12);
LET vMaxDate = CurrentDate+10;
Let vMinDate = ReportStartDate-0;
LET v7days = today()-7;
LET vNxt7days = today()+8;
LET v14days = today()-14;
LET v21days = today()-21;
LET vNxt14days = today()+15;
LET v30days = today()-30;
LET vNxt30days = today()+30;
LET v60days = today()-60;
LET vToday = today();
LET vLastYear = makedate(year(today())-1,month(today()),day(today()));
LET v30daysLastYear = (makedate(year(today())-1,month(today()),day(today())))-30;
LET vNxt30daysLastYear = (makedate(year(today())-1,month(today()),day(today())))+30;
//********************* Temporary Calendar *********************
TempCalendar:
UnQualify *;
LOAD
$(vMinDate)+IterNo()-1 AS Num,
Date(($(vMinDate)+IterNo()-1),'M/D/YYYY') AS TempDate
autogenerate 1
WHILE $(vMinDate)+IterNo()-1<=$(vMaxDate);
MasterCalendar:
LOAD
date(TempDate,'M/D/YYYY') AS OrderDate,
date(yearstart(TempDate,0,7),'YYYY') as FiscalYear,
date(yearstart(TempDate,0,7),'MMM-YY') as FiscalMonth,
date(TempDate) AS FullDate,
if(right(weekname(TempDate, 0, -1),2) = 01 AND year(TempDate) < year (weekend(TempDate, 0, -1))
,53, right(weekname(TempDate, 0,-1),2)) AS Week,
if(weekday(yearstart(TempDate))='Mon' or 'Sun',
if(month(TempDate)='Dec',if(week(TempDate)=1,53,week(TempDate)),week(TempDate)),
if((week(TempDate)-1)=0,53,week(TempDate)-1)) AS WeekofYear,
weekstart(TempDate, 0, -1) & ' - ' & weekend(TempDate, 0, -1) AS WkRange,
weekstart(TempDate, 0, -1) AS WkStart,
weekend(TempDate, 0, -1) AS WkEnd,
month(TempDate) AS Month,
date(TempDate,'MMM-YY') AS MonthYear,
day(TempDate) AS Day,
num(month(TempDate)) & '/' & day(TempDate) AS MonthDay,
dayname(TempDate) AS DayName,
weekday(TempDate) AS Weekday,
year(TempDate) AS Year,
applymap('QuartersMap', num(month(TempDate)), null()) AS Quarter,
applymap('QuartersMap', num(month(TempDate)), null()) & ' - ' & year(TempDate) AS QuarterYear
RESIDENT
TempCalendar;
DROP TABLE TempCalendar;