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

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.

sunny_talwar

How about this

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

mwallman
Creator III
Creator III
Author

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!

sunny_talwar

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)

mwallman
Creator III
Creator III
Author

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

sunny_talwar

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

ecrobles18
Contributor III
Contributor III

Michael,

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

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