Rolling 12 months flag in master calendar script?

Mon, 02 Sep 2013 18:38:21 GMT

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!

Re: Rolling 12 months flag in master calendar script?

v_iyyappan — Mon, 02 Sep 2013 18:51:12 GMT

set the variable:

DateNum = Max(AsofDate)

Rolling 12 Months:

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

Regards,

Re: Rolling 12 months flag in master calendar script?

jjordaan — Mon, 02 Sep 2013 18:58:21 GMT

You can do something like

If((Date <= Today()

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

Re: Rolling 12 months flag in master calendar script?

Mon, 02 Sep 2013 19:49:34 GMT

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))

;

Re: Rolling 12 months flag in master calendar script?

Tue, 03 Sep 2013 12:20:01 GMT

thank you everyone for quick assistance..

Re: Rolling 12 months flag in master calendar script?

Thu, 23 Jan 2014 20:28:17 GMT

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

Re: Rolling 12 months flag in master calendar script?

punitpopli — Mon, 04 Jul 2016 14:12:23 GMT

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

Re: Rolling 12 months flag in master calendar script?

Anonymous — Thu, 01 Nov 2018 15:53:13 GMT

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

topic Rolling 12 months flag in master calendar script? in QlikView

Rolling 12 months flag in master calendar script?

Re: Rolling 12 months flag in master calendar script?

Re: Rolling 12 months flag in master calendar script?

Re: Rolling 12 months flag in master calendar script?

Re: Rolling 12 months flag in master calendar script?

Re: Rolling 12 months flag in master calendar script?

Re: Rolling 12 months flag in master calendar script?

Re: Rolling 12 months flag in master calendar script?