Rolling 12 Variable

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Rolling 12 Variable

Nagaraju_KCS — Tue, 13 Mar 2018 18:13:11 GMT

Try this,..

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

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

Re: Rolling 12 Variable

tchovanec — Tue, 13 Mar 2018 23:13:57 GMT

Daren,

Try This:

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

Re: Rolling 12 Variable

yujiyamane — Tue, 13 Mar 2018 23:55:59 GMT

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

topic Rolling 12 Variable in QlikView

Rolling 12 Variable

Re: Rolling 12 Variable

Re: Rolling 12 Variable

Re: Rolling 12 Variable