Aggregated Rolling 12 months

[#1]

I have data dating back to 2008 by Month and Year.  I want to do a Rolling 12 aggregated total of the field [Account Description]='Total COGS' to be able to use to calculate various Inventory Turn calculations.  I am using the P&L model posted in the Qlik Community as the base model.

I have tried the statement below, but it gives me only the 1 month selected.

=num( Count({1<Month={">=$(=Min(MonthYear))<=$(=Max(MonthYear))"}>} DISTINCT 1) * RangeSum(Above(Sum({1}0) + Sum({$<MonthYear>} Actual), 0, 12)) ,'#,##0')

I have tried the statement below and I get an aggregated total of ALL [Account Description]'s not just 'Total COGS'. 

=num(sum(aggr(rangesum(Below(sum(Actual),0,12)),MonthYear,[Account Description]=('Total COGS'))),'#,##0.#')

Does anyone have any suggestions?  I appreciate your help.

Kelly

stalwar1

[sunny_talwar]

I will try to look at this later today unless someone else is able to help you before me

[#3]

I added the line:  

If(DATE(Year,Period) > ADDMONTHS(TODAY(),-12) and Year,Period <= Today(),1) AS _Rolling12, // Rolling 12

to the script below, but it is still not working as expected.  Do you have recommendations to make the _Rolling12 work?

AccountBalance:
LOAD Site & '_' & Business_Unit &'_'& Metric as AccountMasterKey,
Metric as 'Account Description',
Site,
Business_Unit,
Period,
Year,
Date(MakeDate(Year,Period)) as MonthYear,
Month(MakeDate(Year,Period)) as Month,
If(DATE(Year,Period) > ADDMONTHS(TODAY(),-12) and Year,Period <= Today(),1) AS _Rolling12, // Rolling 12
// 'Q' & Ceil(Month(MakeDate(Year, Period, 1))/3) as Quarter,
  if(isnull(Period),null(),'Q' & Ceil(Month(MakeDate(Year, Period, 1))/3))  as Quarter,
Actual,
AOP,
IsForecast
FROM
$(vPathExternal)TM1-Inventory Data Dump 1 with 2016.xlsx
(ooxml, embedded labels, table is Inventory);
stalwar1‌

[sunny_talwar · Accepted solution]

May be like this

=Num(Sum({<[Account Description] = {'Total COGS'}, MonthYear = {"$(='>' & Date(AddMonths(Max(MonthYear), -12)) & '<=' & Date(Max(MonthYear)))"}, Year, Month, Quarter>} Actual),'#,##0')

[#5]

Thanks again Sunny T!  Worked like a charm!