topic Re: Aggregated Rolling 12 months in QlikView

Aggregated Rolling 12 months

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggregated Rolling 12 months

sunny_talwar — Wed, 21 Sep 2016 20:14:04 GMT

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

Re: Aggregated Rolling 12 months

Thu, 22 Sep 2016 14:18:21 GMT

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‌

Re: Aggregated Rolling 12 months

sunny_talwar — Mon, 26 Sep 2016 19:47:54 GMT

May be like this

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

Re: Aggregated Rolling 12 months

Mon, 26 Sep 2016 19:55:51 GMT

Thanks again Sunny T! Worked like a charm!