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

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

mwallman — Thu, 11 Jan 2018 19:51:06 GMT

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.

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

sunny_talwar — Thu, 11 Jan 2018 20:08:06 GMT

May be just this

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

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

Anonymous — Thu, 11 Jan 2018 20:25:14 GMT

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.

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

mwallman — Thu, 11 Jan 2018 20:25:25 GMT

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?

sunny_talwar — Thu, 11 Jan 2018 20:27:36 GMT

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

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

mwallman — Thu, 11 Jan 2018 20:32:18 GMT

Hi Pavan,

What is between this or stalwar1 code above?

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

Anonymous — Thu, 11 Jan 2018 20:38:48 GMT

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,

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

mwallman — Mon, 15 Jan 2018 10:56:37 GMT

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

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

tresB — Mon, 15 Jan 2018 11:04:41 GMT

Or, you may try with:

InYearToDate()

InMonthToDate()

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

sunny_talwar — Mon, 15 Jan 2018 11:30:24 GMT

This may be

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

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

mwallman — Mon, 15 Jan 2018 11:47:33 GMT

Hi Sunny,

This doesn't quite work because it does Feb 2017 and includes Jan 2018.

I want it to be ALL of January, up to the end of Dec 2017.

So it should not include the current month.

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

sunny_talwar — Mon, 15 Jan 2018 11:50:25 GMT

How about this

If(DateField >= MonthStart(Today(), -12) and DateField < MonthStart(Today()), 1, 0) as R12M_Flag

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

mwallman — Mon, 15 Jan 2018 11:59:11 GMT

Hi Sunny,

Will this include data from the start of the month 12 months ago? i.e. From January 1st? Or Feb 1st? Mar 1st? etc naturally?

This almost work I just want to check the above!

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

sunny_talwar — Mon, 15 Jan 2018 12:02:38 GMT

MonthStart should take your to the beginning of the day... so since today is Jan 15th... we should see this from the above

>= 01/01/2017<01/01/2018

here 01/01/2018 won't be included as we have used less than (and not less than equal to)

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

mwallman — Mon, 15 Jan 2018 12:05:10 GMT

So if I use <= it will include data from the first day of each month?

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

sunny_talwar — Mon, 15 Jan 2018 12:07:21 GMT

No that is not what I meant.... what I trying to say is that since today is Jan 2018... you hopefully want to just include until the last day of Dec 2017, right? If that is true, then the above script should work for you

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

ecrobles18 — Fri, 27 Apr 2018 19:23:28 GMT

Michael,

You still haven't solved this you try the following.

,InMonths(12, D, Today(), -1, num(month(Today()))) as Previous12Months