3 Replies Latest reply: Mar 13, 2018 7:55 PM by Yuji Yamane

# Rolling 12 Variable

Hello All,

I am trying to create a variable in my script for a rolling 12 calculation. I thought I had this figured out, but I cannot get it to work. See my calendar script below, any insight would be much appreciated.

//Calendar Tab

/*************** MinMax Table *************

Keeps minimum and maximum Date value from Facts/ Salesdata table

*/

MinMax:

Min(Date) as MinDate,

Max(Date) as MaxDate

RESIDENT Salesdata;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = \$(vMaxDate);

let vMaxDay   = '=day(max(Date))';

let vMaxMonth  = '=month(max(Date))';

let vMaxYear  = '=max(Year)';

let vPriorYear  = '=vMaxYear-1';

let vPriorYearDate = '=date(addyears(max(Date),-1),' & chr(39) & 'DD MMM YYYY' & chr(39) & ')';

let vPriorYear2  = '=vMaxYear-2';

/*************** Temporary Calendar *************

Generates a single table with one field containing

all existing dates between MinDate and MaxDate.

*/

TempCal:

date(\$(vMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

\$(vMaxDate) - \$(vMinDate) + 1;

DROP TABLE MinMax;

/*************** Master Calendar ****************/

MasterCalendar:

TempDate AS Date,

Week(TempDate) AS Week,

Year(TempDate) AS Year,

Month(TempDate) AS Month,

Day(TempDate) AS Day,

Weekday(TempDate) AS WeekDay,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Date(monthstart(TempDate), 'MM-YYYY') AS MonthYear,

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

inyeartodate(TempDate, \$(vToday), 0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, \$(vToday), -1) * -1 AS LastYTDFlag,

If(Num((TempDate))>=(Num(MonthStart(date(\$(varMaxDate)),-12))),1,0) as R12Flag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

• ###### Re: Rolling 12 Variable

Try this,..

If( TempDate > addmonths(Today(),-12) and TempDate<= Today(),1,0)  as 12Months

or

If( TempDate > addmonths(\$(varMaxDate),-12) and TempDate<= Today(),1,0)  as 12Months

• ###### Re: Rolling 12 Variable

Daren,

Try This:

```IF(Date>= (AddMonths(MonthStart(TODAY()),-12)) AND Date<= (MonthEnd(TODAY(),-1)),1,0) AS ROLLING12MONTHSFLAG
```
• ###### Re: Rolling 12 Variable

Hi Darren,

Accumulation in Qlik is very easy to do at the front end so i would not do anything on load script and do below instead.

Or use below expression.

rangesum(above(sum(Amount),0,12))

Hopefully this helps you.

By the way, the script you supplied looks fine to me. What issue are you having?

Let me know if i can be of any more help.

Yuji