Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tleslie
Contributor II
Contributor II

Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

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". 

Flag 0.PNG

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?

Flag 1.PNG

1 Solution

Accepted Solutions
tleslie
Contributor II
Contributor II
Author

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.

solution.PNG

View solution in original post

9 Replies
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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

tleslie
Contributor II
Contributor II
Author

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.

solution.PNG