Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have Month Year column in MMM - YY format. How do I create Master Calendar using this column? I don't have Date level fields and only this Month Year. I am looking at the Master Calendar Script below but it requires a date to create Min and Max values and generate values in between.
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident Orders;
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 OrderDate,
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;
Try this may be, I tested in text box and it gives me first day of the month, I hardcoded MMM-YY but it should work if you plug-in your field.
Date(Date#('Dec - 23','MMM - YY'),'MM/DD/YYYY')
Other option could be to use MAKEDATE(Y,M,D) but for that you would need numeric mapping of your Month Name and Number and you would need to extract year part from your month year field.
Try this may be, I tested in text box and it gives me first day of the month, I hardcoded MMM-YY but it should work if you plug-in your field.
Date(Date#('Dec - 23','MMM - YY'),'MM/DD/YYYY')
Other option could be to use MAKEDATE(Y,M,D) but for that you would need numeric mapping of your Month Name and Number and you would need to extract year part from your month year field.
Hi ,
You can create reporting date using MAKEDATE(Y,M,D) as Digvijay suggested and use following link to create various flags ytd & rolling periods flags.
Hope this help you.
Vikas
I followed your idea and created dates. When I use this to create my Master Calendar, I found a small issue on how it is creating date buckets such as Quarters. When I select Q1 from 'Quarters', it is selecting first three months from current year as well as Previous year.
Is there a way I can create separate Quarters for each year, which might say Q1 2022, Q2 2022..Q4 2023?
Do something like this -
'Q' & CEIL(MONTH(Yourdatefield) / 3) & '-' & YEAR(Yourdatefield)
Yup! Figured it on my own. Appreciate your help 🙂