topic Re: Assigning a Rolling 12 Month Flag to an AutoGenerated calendar in App Development

Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

tleslie — Tue, 12 Sep 2017 16:51:44 GMT

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?

Re: Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

sunny_talwar — Tue, 12 Sep 2017 16:56:16 GMT

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;

Re: Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

tleslie — Tue, 12 Sep 2017 17:08:17 GMT

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

Re: Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

sunny_talwar — Tue, 12 Sep 2017 17:19:49 GMT

May be use YearToDate function in your calendar script

yeartodate ‒ QlikView‌

YTD(Year to date)

Re: Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

tleslie — Wed, 13 Sep 2017 14:02:48 GMT

And how exactly would this work in the script?

Re: Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

sunny_talwar — Wed, 13 Sep 2017 15:20:10 GMT

Something like this

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

Re: Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

tleslie — Wed, 13 Sep 2017 16:30:35 GMT

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

Re: Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

sunny_talwar — Wed, 13 Sep 2017 16:39:31 GMT

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

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

Re: Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

tleslie — Thu, 14 Sep 2017 18:06:09 GMT

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

Re: Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

tleslie — Thu, 14 Sep 2017 18:18:15 GMT

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.