Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, is there a quick way to add a month and week index to the below master calendar ? For now i only have the date index but I would like to add a month and week index.
TempCalendar:
LOAD
$(varMaxDate) - RowNo() +1 as DateNumber,
Date($(varMaxDate) - RowNo() + 1) as TempDate,
RowNo() as dateIndex
AutoGenerate 1
While $(varMinDate) + IterNo()-1<= $(varMaxDate);
Thanks for the help!
Hi Ashwin,
Try this
TempCalendar:
LOAD
*,
Month(TempDate) AS Month,
Week(TempDate) AS Week;
LOAD
$(varMaxDate) - RowNo() +1 as DateNumber,
Date($(varMaxDate) - RowNo() + 1) as TempDate,
RowNo() as dateIndex
AutoGenerate 1
While $(varMinDate) + IterNo()-1<= $(varMaxDate);
Regards,
Jagan.
Try with Month & Week functions in the script.
Hi Jagan,
Yes, I already have this setup. I was looking for a sequential index with out it being reset in a new year. With current method I would only get 52 weeks for every year. i would like the index to continue on past 52 weeks for the date range. I hope that makes sense. I think I may have to create a separte calendar using autogenerate.
You should not create a new table for this. There are many ways to create sequential numbers inside existing master calendar, e.g.
12*Year(Date) + Month(Date) as SeqMonth
MonthStart(Date) as MonthStartDate
Div(Date,7) as SeqWeekNumber
HIC
Thanks! I did not think of this.