Discussion Board for collaboration on QlikView Scripting.
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.
May be just this
If(DateField >= AddMonths(Today(), -1), 1, 0) as R12M_Flag
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.
Very similar to UI then. Should that be -12 instead of -1 in your example?
Hahahaha yes you are right... I thought I was using AddYears()... but for AddMonths() it should be -12
What is between this or stalwar1 code above?
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,
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
Or, you may try with:
This may be
If(DateField >= AddMonths(Today(), -11), 1, 0) as R12M_Flag