This content has been marked as final. Show 9 replies
Our company has a script that includes an autogenerated calendar that spans between a min and max date.
$(vMinDate) + Iterno()-1 As Num,
Date($(vMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);
Each date is then assigned various calendar dimensions including a flag to indicate which rolling 12 month window in which it belongs. This recreates itself each night during our reload process.
Dual(Text(Date("%Date Key",'YYYY-MMM')),Num(MonthStart("%Date Key"))) as "%Year-Month-R12",
Addmonths("%Date Key", -1-(IterNo()-1)) as "%Date Key"
FROM [$(vQvdSchemaPath)/Periods Dim.qvd] (qvd)
WHILE IterNo() <= 12;
I've noticed that this only works 100% if the current month has 31 days. If the current month does not, such as September, only months with 31 days will have a value populated in "%Year-Month-R12".
I fixed this problem by setting the mode in the AddMonths() function to 1. However, now I am having an issue with values not populating for the 28th day of each month that has 31 days. What am I missing?