Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

tleslie81
New 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
tleslie81
New Contributor II

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

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

9 Replies

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

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;

tleslie81
New Contributor II

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

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

May be use YearToDate function in your calendar script

yeartodate ‒ QlikView

YTD(Year to date)

tleslie81
New Contributor II

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

And how exactly would this work in the script?

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

Something like this

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

tleslie81
New Contributor II

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

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

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

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

tleslie81
New Contributor II

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

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

tleslie81
New Contributor II

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

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

Community Browser