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

[mwallman]

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.

[sunny_talwar]

May be just this

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

[Anonymous]

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]

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]

Hi Pavan,

What is between this or stalwar1 code above?

[Anonymous]

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]

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]

Or, you may try with:

InYearToDate()

InMonthToDate()

[sunny_talwar]

This may be

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