Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

drjones5
New Contributor II

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:

LOAD

       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 vPriorMonth  = '=month(addmonths(max(Date),-1))';

let vPriorMonthYear = '=Year(addmonths(max(Date),-1))';

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:

LOAD

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

AUTOGENERATE

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

DROP TABLE MinMax;

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

MasterCalendar:

LOAD  

      

       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;

3 Replies
qv_testing
Valued Contributor

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

tchovanec
Contributor

Re: Rolling 12 Variable

Daren,

Try This:

IF(Date>= (AddMonths(MonthStart(TODAY()),-12)) AND Date<= (MonthEnd(TODAY(),-1)),1,0) AS ROLLING12MONTHSFLAG

yujiyamane
Contributor II

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.

Capture.PNG

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

Community Browser