Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

mwallman
Contributor

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

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

May be just this

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

pavanamin
New Contributor III

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

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
Contributor

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

Hi Sunny,

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

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

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

mwallman
Contributor

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

Hi Pavan,

What is between this or stalwar1 code above?

pavanamin
New Contributor III

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

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
Contributor

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

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

MVP
MVP

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

Or, you may try with:

InYearToDate()

InMonthToDate()

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

This may be

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

Community Browser