Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

[tleslie]

Our company has a script that includes an autogenerated calendar that spans between a min and max date.

LOAD 

   $(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.

LOAD

    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?

[sunny_talwar]

Why not use MonthStarts instead of MonthEnds?

LOAD

    Dual(Text(Date("%Date Key",'YYYY-MMM')),Num(MonthStart("%Date Key"))) as "%Year-Month-R12",

    MonthStart("%Date Key", -(IterNo()-1)) as "%Date Key"

FROM [$(vQvdSchemaPath)/Periods Dim.qvd] (qvd)

WHILE IterNo() <= 12;

[tleslie]

We are assigning a flag to each date. I tried your code and it only applies to the first day of the month.

[sunny_talwar]

May be use YearToDate function in your calendar script

yeartodate ‒ QlikView‌

YTD(Year to date)

[tleslie]

And how exactly would this work in the script?

[sunny_talwar]

Something like this

If( Date - YearStart(Date) <= Today() - YearStart(Today()), 1, 0 ) as IsInYTD

[tleslie]

Thank you, but I do not believe this satisfies our requirements as I have described.

[sunny_talwar]

My bad, for rolling 12 months, you can do this

If(Date >= AddMonths(Today(), -12) and Date <=  Today(), 1, 0) as IsRolling12Months

[tleslie]

If you refer to our existing script, that also does not satisfy our requirements. Thank you for trying.

[tleslie · Accepted solution]

I believe I have found a solution to the problem. I decided to concatenate two tables into a temp table, one with Mode 1 and other with Mode 0.  I then did a distinct load on the temp table. I now have my desired results, but I am still do not know why using mode 1 would skip over the 28th day.