Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
drjones5
Contributor III
Contributor III

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
Specialist II
Specialist II

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

Daren,

Try This:

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

yujiyamane
Creator II
Creator II

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