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: 
mwallman
Creator III
Creator III

How to create a rolling 12 months flag in the script?

Hello all,

What is the code to create a rolling 12 months flag in the script?

I currently have a Master Calendar and would like to add it to the table but not sure how to create it.

16 Replies
sunny_talwar

May be just this

If(DateField >= AddMonths(Today(), -1), 1, 0) as R12M_Flag

Anonymous
Not applicable

If(Num((TempDate))>=(Num(MonthStart(date($(varMaxDate)),-12))),1,0) as Month12Flag,

add this to the mastercalendar table

I used Creating A Master Calendar | Qlik Community as the mastercalendar sample.

mwallman
Creator III
Creator III
Author

Hi Sunny,

Very similar to UI then. Should that be -12 instead of -1 in your example?

sunny_talwar

Hahahaha yes you are right... I thought I was using AddYears()... but for AddMonths() it should be -12

mwallman
Creator III
Creator III
Author

Hi Pavan,

What is between this or stalwar1 code above?

Anonymous
Not applicable

Same thing. I used MonthStart instead of AddMonths. He posted was while I was testing mine. You can use either. Here is AddMonth using variables from mastercalendar.

If(TempDate >= AddMonths(date($(varMaxDate)), -12), 1, 0) as R12M_Flag,

mwallman
Creator III
Creator III
Author

How can I get it to be complete 12 months?

The code above actually create a 13 month output because of Today() function inside the code.

What I want to do is go up to the end of previous month and take 12 months from there.

So it would be:

From the start of January 2017 to end of Dec 2017

Next month it would be: From the start of February2017 to end of January 2018

From the start of March 2017 to end of February 2017

Thank you

tresesco
MVP
MVP

Or, you may try with:

InYearToDate()

InMonthToDate()

sunny_talwar

This may be

If(DateField >= AddMonths(Today(), -11), 1, 0) as R12M_Flag