Rolling 12 months flag in master calendar script?

[#1]

I am able to calculate YTD, PYTD, PFY and few other flags in mater calendar script.. but need some help to calculate Rolling 12 months flag..

As of today, R12 months flag should be set to 1 for months between Sept. 2012 and Aug. 2013

can someone please help?!

thanks!

[v_iyyappan]

set the variable:

DateNum = Max(AsofDate)

Rolling 12 Months:

=Sum({<Year=, Month=,Quarter=, DateNum={'>=$(=(Num(MonthStart(DateNum,-11))))<=$(=(Num(DateNum)))'}>}Sumfield)

Regards,

[jjordaan]

You can do something like

If((Date <= Today()

  and Date >= MonthStart(Today()-12)),1,0) AS Rolling12Months

[#4 · Accepted solution]

Hi  here is a complete timescript including R12;

Replace TimeField with your date see below and good luck.

/*This script is created to be used as a add on to applications containing dates.

This script do not require any additional datasource.

This script makes it easier to calculate YTD in Layout.

The logic is based on mapping 1 or null to date intervals.

the timewizard in the layout can be used as well to achive similar result. */

// Variables to set YTD and MTD

Let StartDate = num(date(YearStart(AddMonths(Today(),-48,0)),'YYYYMMDD'));

Let EndDate = num(date(YearEnd(AddMonths(Today(),0,0)),'YYYYMMDD'));

Let CurrentDate=num(date(Today(),'YYYYMMDD'));

Let CurrentYear=Year(Today());

Let LY_YTD=num(AddMonths(Today(),-12,0));

Let DaysToEnd=Monthend(Today())-Today();

Let Timespan=Year(Today())-Year(Date($(StartDate)));

Let TimeField='YOUR DATE FIELD HERE';

Time:

Load

    *,

    Year($(TimeField)) AS [Year],

    Month($(TimeField)) AS [Month],

    num(Week($(TimeField)),00) as [Week],

    Day($(TimeField)) AS [Day],

    WeekDay($(TimeField)) AS [WeekDay],

    Year($(TimeField))&num(Month($(TimeField)),00) as [Year Month2],

    if($(TimeField)<=$(CurrentDate),$(TimeField)) as Date2,// Dates until Current date, used for balance dates

    //Date(Monthstart(Date),'MMM-YY') as [Year Month],

    Year($(TimeField))&num(Month($(TimeField)),00) as [Year Month],

    //Dual(WeekYear($(TimeField))&'W'&num(Week(Date),00),WeekYear(Date)&num(Week(Date),00)) as [Year Week],

    Dual(WeekYear($(TimeField))&'W'&num(Week($(TimeField)),00),WeekYear($(TimeField))&num(Week($(TimeField)),00)) as [Year Week2],

    Year($(TimeField))&num(Week($(TimeField)),00) as [Year Week],

    Dual('Q'&Num(Ceil(Num(Month($(TimeField)))/3)),Num(Ceil(NUM(Month($(TimeField)))/3),00)) as [Quarter],

// Calculation Flags

// All flags below can be used in expression for example:

// _YTD_TY, used in Expressions Sum([Purchase Qty]*_YTD_TY)

//  YEAR  

    if(InYear ($(TimeField), $(CurrentDate), -1),1) as _FULL_LY, // All Dates Last Year

    if(InYear ($(TimeField), $(CurrentDate), 0),1) as _FULL_TY, // All Dates This Year

    if(InYearToDate ($(TimeField), $(CurrentDate), 0),1) as _YTD_TY,  // All Dates to Date this Year

    // _YTD_TY, used in Expressions Ex. Sum(Sales*_YTD_TY)

   if(InYearToDate ($(TimeField), $(CurrentDate), -1),1) as _YTD_LY,  // All Dates to Date Last Year

    // _YTD_LY, used in Expressions Ex. Sum(Sales*_YTD_LY)

//  Quarter

    if(InQuarter ($(TimeField), $(CurrentDate), 0),1) as _FULL_TQ_TY, //  All Dates In Current Quarter this Year

    if(InQuarter ($(TimeField), $(LY_YTD), 0),1) as _FULL_TQ_LY, //  All Dates In Current Quarter Last Year

    if(InQuarter ($(TimeField),$(CurrentDate), -1),1) as _FULL_PRQ, //  All Dates In Previous Quarter

    if(InQuarterToDate ($(TimeField), $(CurrentDate), 0),1) as _QTD_TY, //  All Dates In Current Quarter to Date this Year

    if(InQuarterToDate ($(TimeField), $(LY_YTD), 0),1) as _QTD_LY, //  All Dates In Current Quarter to Date Last Year

    if(InQuarterToDate ($(TimeField),$(CurrentDate), -1),1) as _PR_QTD, //  All Dates In Previous Quarter to Date

//  Month  

    If( InMonth ($(TimeField), $(CurrentDate),0),1) as _FULL_TM_TY, // All Dates This Month This Year

    If( InMonth ($(TimeField), $(CurrentDate),-12),1) as _FULL_TM_LY, // All Dates This Month Last Year

    If( InMonth ($(TimeField), $(CurrentDate), -1),1) as _FULL_PRM, // All Dates Previous Month

    If( InMonthToDate ($(TimeField), $(CurrentDate),0),1) as _MTD_TY, // All Dates This Month To Date This Year

    If( InMonthToDate ($(TimeField), $(CurrentDate),-12),1) as _MTD_LY, // All Dates This Month To Date Last Year

    If( InMonthToDate ($(TimeField), $(CurrentDate), -1),1) as _PR_MTD, // All Dates Previous Month To Date

//  Week  

    If( InWeek ($(TimeField), $(CurrentDate),0),1) as _FULL_TW_TY, // All Dates This Week This Year

    If( InWeek ($(TimeField), $(LY_YTD),0),1) as _FULL_TW_LY, // All Dates This Week Last Year

    If( InWeek ($(TimeField), $(CurrentDate), -1),1) as _FULL_PRW, // All Dates Previous Week

    If( InWeekToDate ($(TimeField), $(CurrentDate),0),1) as _WTD_TY, // All Dates This Week To Date This Year

    If( InWeekToDate ($(TimeField), $(CurrentDate), -1),1) as _PR_WTD, // All Dates Previous Week To Date

// Rolling

    If( $(TimeField) > addmonths($(CurrentDate),-12) and $(TimeField) <= Today(),1) as _R12, // Rolling 12

    If( $(TimeField) > addmonths($(CurrentDate),-2) and $(TimeField) <= Today(),1) as _R2, // Rolling 2

    If( $(TimeField) > addmonths($(CurrentDate),-3) and $(TimeField) <= Today(),1) as _R3, // Rolling 3

    If( $(TimeField) > addmonths($(CurrentDate),-6) and $(TimeField) <= Today(),1) as _R6, // Rolling 6

    If( $(TimeField) > addmonths($(CurrentDate),-24) and $(TimeField) <= addmonths($(CurrentDate),-12),1) as _R13_24;// Rolling 13 -24

;

Load

     Date($(StartDate)+(Iterno()-1),'YYYYMMDD') as $(TimeField)

     // Date, to be used as key feild to transactions

Autogenerate 1

While Date($(StartDate)+(Iterno()-1)) <=Date($(EndDate))

;

[#5]

thank you everyone for quick assistance..

[#6]

I like this idea. Could you please upload a sample? Thanks

[punitpopli]

hi BRACOY01‌

Could you please let me know what should be the value for TimeField variable?

I have copied the expression of CurrentDate in TimeField and its not giving me correct result.

Can you please help me in here?

Thanks,

Punit

[Anonymous]

Love the script. I am a Qlik newbie.    I have data were the last date will always be 1 quarter in the past.  So for instance Today is 11/01/2018 but the max date in the file is 6/30/2018.  I will get a new file in January of 2019 and the max date will then be 10/31/2018.   I want to do a rolling 12 months based on the max date of my data and not today's date.  How would I modify your script