Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have month and year fields but my requirement is derive physical quarters and every quarter have 13 weeks of data but I want
showing like Q1-1,2,3.......13
Q2 also like--1,2,3,4,5,........13
every quarter comes like this way
please guide me
Hi Ashok,
Using this post: Creating A Master Calendar
I've built the field
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
40000 as minDate,
42899 as maxDate
AutoGenerate(1);
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
*,
num#(mid(Quarter,2,len(Quarter))) as QuarterNum,
Quarter & '-' & floor(Week/num#(mid(Quarter,2,len(Quarter)))) as [Quarter-Week];
Load
TempDate AS Date,
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,
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;
Which should give you what you need.
If you need to change the starting and ending dates, they're bolded out in the Temp table.
The above code gives me:
Felipe.